-- Please help with making dynamicic SQL as per example below.
-- #Item table for customer
-- #OPERATION_FIELDS table holds the Alias Names for the #Item table
-- I need the Column Headers from the PIVOT output or Alias to be meaningful.eexample ...
-- [Product_Code] [Level EACH] [Description EACH] [GTIN EACH] ..... [Length EACH] etc and NOT
-- [itm$User_Item EACH] itm$User_Type EACH] [itm$User1 EACH] [itm$User2 EACH]..... [itm$Length INNER] etc
-- I also only want to SELECT the highest or Max itm$User_Sequence number
-- Thanks in advance
-- Kind regards David Geoghegan
--ITEM Table
CREATE TABLE #item(
[ITM$User_Item] varchar NOT NULL,
[ITM$User_Type] varchar NOT NULL DEFAULT ('EACH'),
[ITM$User_Sequence] [int] NOT NULL DEFAULT ((0)),
[ITM$User1] varchar NULL DEFAULT (''),
[ITM$User2] varchar NULL DEFAULT (''),
[ITM$User3] varchar NULL DEFAULT (''),
[ITM$User4] varchar NULL DEFAULT (''),
[ITM$User5] varchar NULL DEFAULT (''),
[ITM$User6] varchar NULL DEFAULT (''),
[ITM$User7] varchar NULL DEFAULT (''),
[ITM$User8] varchar NULL DEFAULT (''),
[ITM$Length] [numeric](7, 2) NOT NULL DEFAULT ((0)),
[ITM$Width] [numeric](7, 2) NOT NULL DEFAULT ((0)),
[ITM$Height] [numeric](7, 2) NOT NULL DEFAULT ((0)),
[ITM$Weight] [numeric](10, 3) NOT NULL DEFAULT ((0)),
[ITM$User_Date_Time] [datetime] NOT NULL DEFAULT (getdate()),
[ITM$User_Scan_Indicator] varchar NOT NULL DEFAULT ('N'),
[ITM$Exported] [int] NOT NULL DEFAULT ((0)))
INSERT INTO #Item
([ITM$User_Item],[itm$User_Type],[ITM$User_Sequence],[itm$User1], [itm$User2],[itm$User3],[itm$User_Date_Time],[itm$Length],[itm$Width],[itm$Height],[itm$Weight],[itm$Exported],[itm$User_Scan_Indicator])
VALUES
('S100', 'EACH',1,'COR CST MODENA ALB 50PG DRYMOUNT BLACK','9311960353701',1, '2016-01-01 14:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('S100', 'INNER',1,'COR CST MODENA ALB 50PG DRYMOUNT BLACK','9311960353702',10, '2016-02-01 16:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('S100', 'OUTER',1,'COR CST MODENA ALB 50PG DRYMOUNT BLACK','9311960353701',100, '2016-02-01 16:23:00',0,0,0,0,0,'Y'),
('C100', 'EACH',1,'ESSELTE SIGN/MENU HOLDER 2 SIDED PORT DL','9311960353701',1, '2016-01-01 14:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('C100', 'INNER',1,'ESSELTE SIGN/MENU HOLDER 2 SIDED PORT DL','9311960353702',10, '2016-02-01 16:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('C100', 'OUTER',1,'ESSELTE SIGN/MENU HOLDER 2 SIDED PORT DL','9311960353701',100, '2016-02-01 16:23:00',0,0,0,0,0,'Y'),
('A100', 'EACH',1,'FOB','9326303028758',1, '2016-01-01 14:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('A100', 'EACH',2,'FOB','9326303028758',1, '2016-01-01 14:00:00',5.1,10.2,15.4,10.46,0,'Y'),
('A100', 'INNER',1,'FOB','9326303028759',10, '2016-02-01 16:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('A100', 'OUTER',1,'FOB','9326303028757',100, '2016-02-01 16:23:00',0,0,0,0,0,'Y')
--Select * from #item
-- OPERATION_FIELDS Table used to store Alias and wether Hidden information
CREATE TABLE #OPERATION_FIELDS(
[OPF$FIELD_NAME] varchar NOT NULL DEFAULT (''),
[OPF$CAPTION] varchar NULL DEFAULT (''),
[OPF$HIDE] varchar NULL DEFAULT ('False'),
[OPF$EXPORT_ORDER] [int] NULL DEFAULT ((0)))
INSERT INTO #OPERATION_FIELDS
([OPF$FIELD_NAME]
,[OPF$CAPTION]
,[OPF$HIDE]
,[OPF$EXPORT_ORDER]
)
VALUES
('itm$User_Item','Product_Code','False',1),
('itm$User_Type','Level','False',2),
('itm$User_Sequence','Sequence','True',0),
('itm$User1','Description','False',3),
('itm$User2','GTIN','False',4),
('itm$User3','QTY_Per_Level','False',5),
('itm$User4','User4','True',0),
('itm$User5','User5','True',0),
('itm$User6','User6','True',0),
('itm$User7','User7','True',0),
('itm$User8','User8','True',0),
('itm$Length','Length','False',6),
('itm$Width','Width','False',7),
('itm$Height','Height','False',8),
('itm$Weight','Weight','False',9),
('itm$User_Date_Time','DateTime','False',10),
('itm$Exported','Exported','True',0),
('itm$User_Scan_Indicator','Scan_Indicator','False',11)
--select * from #OPERATION_FIELDS
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
-- Dump to get Meaningful Column names or Alias
select @cols = STUFF((SELECT ',' + QUOTENAME(col)
from #OPERATION_FIELDS
cross apply
(
-- select OPF$FIELD_NAME, 1 union all
select OPF$CAPTION, 2
) c (col, so) Where OPF$HIDE = 'False'
group by col, so,OPF$EXPORT_ORDER
order by OPF$EXPORT_ORDER,so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
print @cols
select @cols = STUFF((SELECT ',' + QUOTENAME(col+ ' ' + itm$User_Type)
from #Item
cross apply
(
select 'Level', 1 union all
select 'itm$User1', 2 union all
select 'itm$User2', 3 union all
select 'itm$User3', 4 union all
select 'itm$User_Date_Time', 5 union all
select 'itm$Length', 6 union all
select 'itm$Width', 7 union all
select 'itm$Height', 8 union all
select 'itm$Weight', 9 union all
select 'itm$Exported', 10 union all
select 'itm$User_Scan_Indicator', 11
) c (col, so)
group by col, so, itm$User_Type
order by CASE itm$User_Type WHEN 'EACH' THEN 1 WHEN 'INNER' THEN 2 WHEN 'OUTER' THEN 3 ELSE 4 END, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
Print @cols
set @query = 'SELECT itm$User_Item As [Product_Code],' + @cols + '
from
(
select itm$User_Item, col+ '' '' + itm$User_Type col, value
from #Item
cross apply
(
select ''Level'', itm$User_Type union all
select ''itm$User1'', [itm$User1] union all
select ''itm$User2'', [itm$User2] union all
select ''itm$User3'', [itm$User3] union all
select ''itm$User_Date_Time'', convert(varchar(10), itm$User_Date_Time, 120) union all
select ''itm$Length'', cast(itm$Length as varchar(100)) union all
select ''itm$Width'', cast(itm$Width as varchar(100)) union all
select ''itm$Height'', cast(itm$Height as varchar(100)) union all
select ''itm$Weight'', cast(itm$Weight as varchar(100)) union all
select ''itm$Exported'', cast(itm$Exported as varchar(100)) union all
select ''itm$User_Scan_Indicator'', [itm$User_Scan_Indicator]
) c (col, value)
WHERE [itm$User_Scan_Indicator] = ''Y''
AND itm$Exported = 0
) x
pivot
(
max(value)
for col in (' + @cols + ')
) p
'
print @query
execute sp_executesql @query;
drop TABLE #item
drop TABLE #OPERATION_FIELDS