Traspose rows in columns

Transact-SQL
1

Hello,
I have a Select from a legacy view like this:

SELECT PositionCode, FormatCode, PubDate, Client, Sold, Active
FROM [dbo].[v_FixedPositions]

that I query with a range of date:

SELECT PositionCode, FormatCode, PubDate, Client, Sold, Active
FROM [dbo].[v_FixedPositions]
WHERE PubDate BETWEEN '2020-05-28' AND '2020-05-29'

and I obtain:

indent preformatted text by 4 spaces

PositionCode FormatCode PubDate Cliente Sold Active
PP FINDX 2020-05-28 BARILLA 1 1
PP FINSX 2020-05-28 Client1 1 0
PP MTDX 2020-05-28 Blu 0 1
PP MTSX 2020-05-28 Fiat 0 1
ATT 3X2_EXT 2020-05-28 Vast 0 1
UP MD6X28 2020-05-28 Fiat 1 0
PP FINDX 2020-05-29 RCS 1 0
PP FINSX 2020-05-29 RCS 0 1
PP MTDX 2020-05-29 Bull 0 1
PP MTSX 2020-05-29 Vast 0 1
ATT 3X2_EXT 2020-05-29 Blue 0 1
UP MD6X28 2020-05-29 Vast 0 1

Now I have to traspose date in columns, like this:

indent preformatted text by 4 spaces

PositionCode FormatCode 2020-05-28 2020-05-29
PP FINDX BARILLA RCS
PP FINSX Client1 RCS
PP MTDX Blu Bull
PP MTSX Fiat Vast
ATT 3X2_EXT Vast Blue
UP MD6X28 Fiat Vast

How can I accomplish this, in SQL Server 2017?

Thanks a lot in advance.

Luis

2

hi

please see below link .. hope it helps :slight_smile:

https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

if you don't have enough experience to implement it ..please let me know
i can do it for you !!!

3

Here what I have to obtain (in image is bette comprehension).

image
image851×616 12.3 KB

4

hi

Hope this helps :slight_smile:

please click arrow to the left for CREATE Data 
create table #temp 
(
PositionCode varchar(10),
FormatCode varchar(10),
PubDate date ,
Cliente varchar(10),
Sold int ,
Active int 
)
go 


insert into #temp select  'PP','FINDX	','2020-05-28','BARILLA ',1,1
insert into #temp select  'PP','FINSX	','2020-05-28','Client1 ',1,0
insert into #temp select  'PP' ,'MTDX	','2020-05-28','Blu		',0,1
insert into #temp select  'PP' ,'MTSX	','2020-05-28','Fiat	',0,1
insert into #temp select  'ATT','3X2_EXT ','2020-05-28','Vast	',0,1
insert into #temp select  'UP' ,'MD6X28	','2020-05-28','Fiat	',1,0
insert into #temp select  'PP' ,'FINDX	','2020-05-29','RCS		',1,0
insert into #temp select  'PP' ,'FINSX	','2020-05-29','RCS		',0,1
insert into #temp select  'PP' ,'MTDX	','2020-05-29','Bull	',0,1
insert into #temp select  'PP' ,'MTSX	','2020-05-29','Vast	',0,1
insert into #temp select  'ATT','3X2_EXT ','2020-05-29','Blue	',0,1
insert into #temp select  'UP' ,'MD6X28	','2020-05-29','Vast	',0,1
go

select 'SQL Output',
	PositionCode
	, FormatCode
	, max(case when PubDate = '2020-05-28' then Cliente end) as '2020-05-28'
	, max(case when PubDate = '2020-05-29' then Cliente end)  as '2020-05-29' 
from #temp
group by PositionCode,FormatCode
go

image

2 Likes
5

I think could be fine. Thank you so much Harishgg.

L.