I am trying to create a cross-tab report to show progress of Data Enrichment on a Vendors spreadsheet which is imported into SQL via SSIS. The data is
date_added stat-Desc stat_value
2020-01-27 Address 1 - missing items 267
2020-01-27 Bank Account - Invalid Items 46
2020-01-31 Address 1 - missing items 201
2020-01-31 Bank Account - Invalid Items 42
What I would like to produce is a report as follows:
stat-Desc 2020-01-27 2020-01-31
Address 1 - missing items 267 201
Bank Account - Invalid Items 46 42
etc
Any ideas? I have tried a PIVOT query but keep getting dates instead of stat values, but everything else was ok
use sqlteam
go
create table #paulricot(date_added varchar(50),
stat_desc varchar(50), stat_value int)
insert into #paulricot
select '2020-01-27', 'Address 1 - missing items', 267 union
select '2020-01-27', 'Bank Account - Invalid Items', 46 union
select '2020-01-31', 'Address 1 - missing items', 201 union
select '2020-01-31', 'Bank Account - Invalid Items', 42
select stat_desc, [2020-01-27], [2020-01-31] from
(
select stat_desc, stat_value, date_added
From #paulricot
) p
PIVOT
(
MAX (stat_value)
FOR date_added in ( [2020-01-27], [2020-01-31])
) as pvt
drop table #paulricot
or dynamically
declare @cols nvarchar(max), @query nvarchar(max);
create table #paulricot(date_added varchar(50),
stat_desc varchar(50), stat_value int)
insert into #paulricot
select '2020-01-27', 'Address 1 - missing items', 267 union
select '2020-01-27', 'Bank Account - Invalid Items', 46 union
select '2020-01-31', 'Address 1 - missing items', 201 union
select '2020-01-31', 'Bank Account - Invalid Items', 42 union
select '2020-01-09', 'Bank Roberry- Stick''em up', 80000
SELECT @cols = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM ( SELECT N'[' + a.date_added + '], '
FROM ( SELECT DISTINCT date_added
FROM #paulricot r ) a
ORDER BY a.date_added
FOR XML PATH ( '' ) ) b ( Piv );
set @query = 'SELECT stat_desc, ' + @cols + ' from
(
select stat_desc, stat_value, date_added
from #paulricot
) x
pivot
(
MAX(stat_value)
for [date_added] in (' + @cols + ')
) p '
exec(@query)
1 Like
Awesome! Thanks Yosiasz - ran the second script as is and looks very good.
An "expert" colleague said pivoting the date cols dynamically was impossible but I absolutely knew that if MS Access could do it, so can SQL!
Will test on "real" data.
PS. How's the Bank Roberry going?