Loading

Convert Rows to Column

Convert Rows to Column

ashish gupta

8y924

I am getting output from my query is :-

Dt	Column	Column1
2018-07- 09	Wet & dry1	2/2
2018-07-09	Back Pack	4/5
2018-07-09	Ride on	4/8
2018-07-09	Walk behind	4/4
2018-07-09	Test	3/6

I want result in below format :-

Dt	Wet & dry1	Back Pack	Ride on	Walk behind	Test
2018-07-09	2/2	4/5	4/8	4/4	3/6

Please help ,and column is dyamic.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
Try following:
declare @someTable table
(
   [dt] date not null
  ,[Column] varchar(20) not null
  ,[column1] varchar(5) not null
)

insert into @someTable ([dt], [Column], [column1]) values
('2018-07-09', 'Wet & dry1', '2/2')
,('2018-07-09', 'Back Pack', '4/5')
,('2018-07-09', 'Ride on', '4/8')
,('2018-07-09', 'Walk behind', '4/4')
,('2018-07-09', 'Test', '3/6')

select *
from @someTable
pivot
(
  max([column1])
  for [Column] in ([Wet & dry1], [Back Pack], [Ride on], [Walk behind], [Test])
) as pivoted
ashish gupta8y ago
Thanks Nitin ji,this solution will work for me.
Aakash Maurya8y ago
Will this article work for you?

https://www.c-sharpcorner.com/article/convert-rows-to-columns-using-dynamic-pivot-in-sql-server/

http://www.sqlrelease.com/dynamic-pivot-query-in-sql-server

Let me know