SQLTeam.com | Weblogs | Forums

Transpose rows in columns

Hello,
I have a view that shows me a list of fairs, the cities where the fair is held
and the date. Something like this:

FAIR - CITY - DATE
FairBlu - Boston - 2020-07-17
FairBlu - Chicago - 2020-07-19
FairBlu - NewYork - 2020-07-23
FairGreen - Chicago - 2020-07-21
FairGreen - SanFrancisco - 2020-07-22
FairRed - NewYork - 2020-07-21
FairRed - Washington - 2020-07-20
FairRed - Washington - 2020-07-22

......

Now I have to transpose the dates in rows in columns, for a week span (for example),
to obtain something like this:

FAIR - CITY - 2020-07-17 - 2020-07-18 - 2020-07-19 - 2020-07-20 - 2020-07-21 - 2020-07-22 - 2020-07-23

FairBlu - Boston - X - Null - Null - Null - Null - Null - Null
FairBlu - Chicago - Null - Null - X - Null - Null - Null - Null
FairBlu - NewYork - Null - Null - Null - Null - Null - Null - X
FairGreen - Chicago - Null - Null - Null - Null - X - Null - Null
FairGreen - SanFrancisco - Null - Null - Null - Null - Null - X - Null
FairRed - NewYork - Null - Null - Null - Null - X - Null - Null
FairRed - Washington - Null - Null - Null - X - Null - Null - Null
FairRed - Washington - Null - Null - Null - Null - Null - X - Null

How can I accomplish this strange matrix?

Thank you in advance.

Luigi

Please provide sampoe data in real ddl and dml

Create table sample

Insert into sample

hi Luigi

Are you Italian ?? Sorry for asking just curious ..

I tried to do this .. hope its what you are looking for

please click arrow to the left for drop create data script
----------------------
-- create table 

create table Data
(
FAIR varchar(100),
CITY varchar(100),
DATE1 date 
)

go 

---------------------------
-- insert into table 

insert into data select 'FairBlu   ','Boston			','2020-07-17'
insert into data select 'FairBlu   ','Chicago		','2020-07-19'
insert into data select 'FairBlu   ','NewYork		','2020-07-23'
insert into data select 'FairGreen ','Chicago		','2020-07-21'
insert into data select 'FairGreen ','SanFrancisco	','2020-07-22'
insert into data select 'FairRed   ','NewYork		','2020-07-21'   
insert into data select 'FairRed   ','Washington		','2020-07-20'
insert into data select 'FairRed   ','Washington		','2020-07-22'

go
declare @week_start date =  '2020-07-17' 

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

; with cte as 
(
    SELECT 
	      top 7 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 as Rn
    FROM 
	     sys.all_columns a 
		    CROSS JOIN 
		 sys.all_columns b
) 
SELECT  @cols = STUFF(( select  distinct ',' +  QUOTENAME(dateadd(dd,rn,@week_start)) from cte c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

SET @query = 'SELECT FAIR,city, ' + @cols + ' from  ( select FAIR,city,Date1 from data ) x     pivot (max(date1) for Date1 in (' + @cols + ') ) p '

execute(@query)

1 Like

I know it was a little complicated.
As always, thank you very much Harish.
And yes, I'm Italian :it:

benvenuto

:slight_smile:

1 Like

Grazie mille :slight_smile:

What is there are 2 trips to the ssme place in the same day

Yes is possible, in that case appear X in both columns.

L

so you want the same date to appear twice as a column?
2020-07-03 | 2020-07-03

use sqlteam
go

create table #pastaalforno(FAIR nvarchar(50), CITY nvarchar(50), tripdate date)

insert into #pastaalforno
select 'FairBlu','Boston','2020-07-17' union
select 'FairBlu','Chicago','2020-07-19' union
select 'FairBlu','NewYork','2020-07-23' union
select 'FairGreen','Chicago','2020-07-21' union
select 'FairGreen','SanFrancisco','2020-07-22' union
select 'FairRed','NewYork','2020-07-21' union
select 'FairRed','Washington','2020-07-20' union all
select 'FairRed','Washington','2020-07-22' union all
select 'FairRed','Washington','2020-07-22' --second trip to Washington, same date

declare @cols nvarchar(max), @query nvarchar(max);

SELECT  @cols = STUFF(( select  distinct ',' +  QUOTENAME(tripdate) 
from #pastaalforno c 
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'select fair, city, ' + @cols + '
 from (
	select fair
		,CITY
		,tripdate
		,count(1) as _tripCount
	from #pastaalforno
	group by fair,CITY,tripdate
)x
pivot 
(
sum(_tripCount)
for tripdate in (' + @cols + ')
) p '

execute(@query)



drop table #pastaalforno