Transpose rows in columns

1

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

2

Please provide sampoe data in real ddl and dml

Create table sample

Insert into sample

3

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)

image
image703×660 20.5 KB

1 Like
4

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

5

benvenuto

:slight_smile:

1 Like
6

Grazie mille :slight_smile:

7

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

8

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

L

9

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

image
image801×199 5.64 KB

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