How to group by and split columns dynamically with sql query?

Question

I have some table contains rows like below;

ID Date City     Income
1  2013 Kansas   10$
1  2013 Kansas   15$
1  2014 Kansas   30$
2  2013 Chicago  50$
...

I need such a query that transform it to this;

ID Date City     Income1 Income2
1  2013 Kansas   10$     15$
1  2014 Kansas   30$
2  2013 Chicago  50$
...

So I should group by ID, Date then split Income values to different columns.. This is how I try to achieve it but some thing not right;

select ID, DATE, MIN(CITY), CONCAT(INCOME,',') from [Sheet 1$] group by ID, DATE

Is the number of entries per city and year fixed, or is it variable? If the latter, then you would probably need dynamic SQL to handle it. — Tim Biegeleisen, Aug 24 '17 at 08:17
Possible duplicate of [How to use group by query in Excel via C#](https://stackoverflow.com/questions/45855840/how-to-use-group-by-query-in-excel-via-c-sharp) You already have this as a question on stackoverflow - surely you need to decide where your data is - as this is exactly the same — BugFinder, Aug 24 '17 at 08:27
and how many fixed entries are per city and year, maximum 2 ? — Oto Shavadze, Aug 24 '17 at 08:28
@BugFinder but this is full sql concept post, I will remove other, once it solved — TyForHelpDude, Aug 24 '17 at 08:28
But ive already told you how to fix the SQL that applies to this too!! — BugFinder, Aug 24 '17 at 08:29
@BugFinder TOLD? what is told? this is StackOverflow not snap or chat application, I am already trying handle it that way but its not an answer that can accept for my issue — TyForHelpDude, Aug 24 '17 at 08:31
For what i understand, you need to use a pivot. But if you have an unknown number of columns you must use variables for sure. — Axiome, Aug 24 '17 at 08:51

score 2 · Accepted Answer · edited Aug 24 '17 at 09:51

Create table Table1(ID int, Year int, City Varchar(10), Income int)


Insert Into Table1 Values(1,  2013, 'Kansas',   10)
Insert Into Table1 Values(1,  2013, 'Kansas',   10)
Insert Into Table1 Values(1,  2013, 'Kansas',   15)
Insert Into Table1 Values(1,  2014, 'Kansas',  30)
Insert Into Table1 Values(2,  2013, 'Chicago',  50)

Select max(rn) as MaxCol
From(
        Select *, row_number() over(partition by ID,Year order by ID) as rn
        From Table1
    )as Tbl1


select *
from 
(
  Select *, row_number() over(partition by ID,Year order by ID) as rn
  from Table1
) src
pivot
(
  max(Income)
  for rn in ([1], [2], [3])
) piv;

Actually dynamic sql is only option when number of Income value(Count) per year is not fixed.

Link to a live demo on SQL Fiddle

I agree, the problem is close to this one : https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query. You can't get rid of it without variables. — Axiome, Aug 24 '17 at 09:10

How to group by and split columns dynamically with sql query?

1 Answers1