-- Please help me with this Pivot table output.
--1: I would like the output Alias to have the Pivot column header concatinated with 'Level'.
-- Example instead of 'Level 1' or 'GTIN 2' I would like to have column Header like 'Level EACH' and 'GTIN OUTER' etc
--2: Return results when [Scan Indicator] = 'Y'
--3: Return results when [Exported ] = 0
--4: Return NULL data as either 0 for numeric or '' Empty for charactor data.
--5: Is there a better way to do this query?
--6: Thank you in advance for feedback
--7: Kind regards, David Geoghegan
/*
Item Description Level 1 Level Description 1 GTIN 1 QTY per Level 1 ScanDate 1 Length 1 Width 1 Height 1 Weight 1 Exported 1 Scan Indicator 1 Level 2 Level Description 2 GTIN 2 QTY per Level 2 ScanDate 2 Length 2 Width 2 Height 2 Weight 2 Exported 2 Scan Indicator 2 Level 3 Level Description 3 GTIN 3 QTY per Level 3 ScanDate 3 Length 3 Width 3 Height 3 Weight 3 Exported 3 Scan Indicator 3 Level 4 Level Description 4 GTIN 4 QTY per Level 4 ScanDate 4 Length 4 Width 4 Height 4 Weight 4 Exported 4 Scan Indicator 4
*/
CREATE TABLE #ItemTbl
([Item] varchar(50)
, [itmIndex] int
, [Description] varchar(100)
, [Level] varchar(50)
, [Level Description] varchar(100)
, [GTIN] varchar(100)
, [QTY per Level] int
, [ScanDate] datetime DEFAULT GETDATE()
, [Length] numeric(5,2)
, [Width] numeric(5,2)
, [Height] numeric(5,2)
, [Weight] numeric(5,2)
, [Exported] int DEFAULT 0
, [Scan Indicator] varchar(1) DEFAULT 'N')
;
INSERT INTO #ItemTbl
([Item], [itmIndex],[Description], [Level],[Level Description],[GTIN],[QTY per Level], [ScanDate],[Length],[Width],[Height],[Weight],[Exported],[Scan Indicator])
VALUES
('S100', 1, 'Office Supply','EACH','Pencil','9311960353701',1, '2016-01-01 14:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('S100', 2, 'Office Supply','INNER','Pencil','9311960353702',10, '2016-02-01 16:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('S100', 3, 'Office Supply','OUTER','Pencil','9311960353701',100, '2016-02-01 16:23:00',0,0,0,0,0,'N'),
('C150', 1, 'School','EACH', 'Sharpener','9556091211234',1,'2016-01-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('C150', 2, 'School','INNER','Sharpener','9556091211234',25, '2016-02-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('C150', 3, 'School','OUTER','Sharpener','9556091211234',200, '2016-02-01 00:00:00',0,0,0,0,0,'N'),
('A200', 1, 'Stationary','EACH', 'A4 Paper','9556091211234',1,'2016-01-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('A200', 2, 'Stationary','INNER','A4 Paper','9556091211234',25, '2016-02-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('A200', 3, 'Stationary','OUTER','A4 Paper','29311960004379',200, '2016-02-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('C250', 1, 'Stationary','EACH', 'A4 Paper','29311960004379',1,'2016-01-01 00:00:00',0,0,0,100.50,0,'N'),
('C250', 2, 'Stationary','INNER','A4 Paper','29311960004379',25, '2016-02-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('C250', 3, 'Stationary','OUTER','A4 Paper','29311960004379',200, '2016-02-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('O300', 1, 'School','EACH', '48 Page Lined Exersize book','29311960004379',1,'2016-01-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('O300', 2, 'School','INNER','48 Page Lined Exersize book','29311960004379',25, '2016-02-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('O300', 3, 'School','OUTER','48 Page Lined Exersize book','29311960004379',200, '2016-02-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y'),
('O300', 4, 'School','CASE','48 Page Lined Exersize book','9311960006447',500, '2016-02-01 00:00:00',10.0,20.1,30.2,10.46,0,'Y')
;
Select * from #ItemTbl
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col+ ' ' + cast(itmIndex as varchar(10)))
from #ItemTbl
cross apply
(
select 'Level', 1 union all
select 'Level Description', 2 union all
select 'GTIN', 3 union all
select 'QTY per Level', 4 union all
select 'ScanDate', 5 union all
select 'Length', 6 union all
select 'Width', 7 union all
select 'Height', 8 union all
select 'Weight', 9 union all
select 'Exported', 10 union all
select 'Scan Indicator', 11
) c (col, so)
group by col, so, itmIndex
order by itmIndex, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
Print @cols
set @query = 'SELECT Item, Description,' + @cols + '
from
(
select Item, Description, col+ '' '' + cast(itmIndex as varchar(10)) col, value
from #ItemTbl
cross apply
(
select ''Level'', Level union all
select ''Level Description'', [Level Description] union all
select ''GTIN'', [GTIN] union all
select ''QTY per Level'', cast([QTY per Level] as varchar(100)) union all
select ''ScanDate'', convert(varchar(10), ScanDate, 120) union all
select ''Length'', cast(Length as varchar(100)) union all
select ''Width'', cast(Width as varchar(100)) union all
select ''Height'', cast(Height as varchar(100)) union all
select ''Weight'', cast(Weight as varchar(100)) union all
select ''Exported'', cast(Exported as varchar(100)) union all
select ''Scan Indicator'', [Scan Indicator]
) c (col, value)
) x
pivot
(
max(value)
for col in (' + @cols + ')
) p '
print @query
execute sp_executesql @query;
Drop table #ItemTbl