sql - Query with rows into columns -

i have data in tbl1:

item    date    amount   1   10   2   20   3   30   4   40 b   1   20 b   2   30 b   3   40 b   4   50 c   1   30 c   2   40 c   3   50 c   4   60 

but need below

item    1   2   3   4   10  20  30  40 b   20  30  40  50 c   30  40  50  60 

you can use group by conditional aggregation using case this.

sample data

declare @tbl1 table (item char(1),date int, amount int) insert @tbl1 values ('a',   1,   10), ('a',   2,   20), ('a',   3,   30), ('a',   4,   40), ('b',   1,   20), ('b',   2,   30), ('b',   3,   40), ('b',   4,   50), ('c',   1,   30), ('c',   2,   40), ('c',   3,   50), ('c',   4,   60); 

query

select item, max(case when date = 1 amount end) d1, max(case when date = 2 amount end) d2, max(case when date = 3 amount end) d3, max(case when date = 4 amount end) d4 @tbl1 group item 

output

item    d1  d2  d3  d4   10  20  30  40 b   20  30  40  50 c   30  40  50  60 

edit if have unknown number of date, use dynamic pivot this.

declare @s nvarchar(max)   select @s  = stuff((select distinct ',' + quotename(convert(varchar(10),date),'[')   #tbl1 xml path('')),1,1,'')  set @s = n'select item,' + @s + ' #tbl1 pivot(max(amount) date in(' + @s + ')) pvt' print @s exec sp_executesql @s