SQLTeam.com | Weblogs | Forums

Help with Cross Tab view in SQL-T

#1

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

#2 
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
#3

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)
#4

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? :wink: