Converting multiple rows to multiple columns in SQL

Question

I have a data something like this:

Team    Month   Count_of_Stores
  a      4       10
  b      4        4
  c      4        6
  a      5        8
  b      5       14
  e      5        9
  a      6        7
  b      6        3
  f      6        1

I working to get an output something like this converting the rows to columns:

Team Month Count_of_Stores Team Month Count_of_Stores Team Month Count_of_Stores a 4 10 a 5 8 a 6 7 b 4 4 b 5 14 b 6 3 c 4 6 e 5 9 f 6 1

I am sure pivot should be of great help here, but confused in the appropriate usage here. any help is much appreciated.

You can use conditional aggregation here but how do you know the order of the results? You have no way in the data posted to order the results. — Sean Lange
– Sean Lange, Commented Sep 17, 2018 at 21:32
@SeanLange I actually do group it based on another criteria which is teams which i haven't mentioned here. So in that way the data is being ordered and grouped. — Tamil
– Tamil, Commented Sep 17, 2018 at 21:35
OK but with the data you provided it is impossible to be consistent with ordering. Perhaps you can share some more details? Here is a great place to start. — Sean Lange
– Sean Lange, Commented Sep 18, 2018 at 12:57
@SeanLange Thanks sean! Thats right. I think my data is incomplete and the code suggested by "IsItGreyOrGray" looks almost close below. What do you think? — Tamil
– Tamil, Commented Sep 18, 2018 at 15:01
The code provided in that answer is about all you can get with no way to ensure ordering. In other words you will get values for months 4,5 and 6 but have no way to know which value of each will appear next to each other. Meaning one time you may get 4,10-5,8-6,7 and another get 4,6-5,14-6,7 on the same row. — Sean Lange
– Sean Lange, Commented Sep 18, 2018 at 15:04

GreyOrGray · Accepted Answer · 2018-09-18 16:17:37Z

1

I'm inferring team association based on the data provided. Something like the below could work for you (demo: http://rextester.com/GQHPV34978)

CREATE TABLE temp
(
    [teamID] INT,
    [month] INT,
    [count_of_stores] INT
)

INSERT INTO temp
VALUES 
(1,4,10),
(2,4,4),
(3,4,6),
(1,5,8),
(2,5,14),
(3,5,9),
(1,6,7),
(2,6,3),
(3,6,1)

SELECT [teamID], 
    MAX(CASE WHEN MONTH = 4 THEN MONTH END )AS Month,
    MAX(CASE WHEN MONTH = 4 THEN count_of_stores END )  AS Count_of_Stores,
    MAX(CASE WHEN MONTH = 5 THEN MONTH END )AS Month,
    MAX(CASE WHEN MONTH = 5 THEN count_of_stores END )  AS Count_of_Stores ,
    MAX(CASE WHEN MONTH = 6 THEN MONTH END )AS Month,
    MAX(CASE WHEN MONTH = 6 THEN count_of_stores END )  AS Count_of_Stores 
FROM temp
GROUP BY teamID

Updating with the following based on new information (demo:http://rextester.com/JIZQX61960)

create TABLE #temp
(
    [teamID] varchar,
    [month] INT,
    [count_of_stores] INT
)

INSERT INTO #temp
VALUES 
('a',4,10),
('b',4,4),
('c',4,6),
('a',5,8),
('b',5,14),
('e',5,9),
('a',6,7),
('b',6,3),
('f',6,1);


WITH monthGrouping AS
(
    SELECT row_number() over (partition by month order by month) as rn, [teamID], [month],[count_of_stores] FROM #temp
)

SELECT 
    MAX(CASE WHEN MONTH = 4 THEN [teamID] END )AS [teamID],
    MAX(CASE WHEN MONTH = 4 THEN MONTH END )AS Month,
    MAX(CASE WHEN MONTH = 4 THEN count_of_stores END )  AS Count_of_Stores,
    MAX(CASE WHEN MONTH = 5 THEN [teamID] END )AS [teamID],
    MAX(CASE WHEN MONTH = 5 THEN MONTH END )AS Month,
    MAX(CASE WHEN MONTH = 5 THEN count_of_stores END )  AS Count_of_Stores ,
    MAX(CASE WHEN MONTH = 6 THEN [teamID] END )AS [teamID],
    MAX(CASE WHEN MONTH = 6 THEN MONTH END )AS Month,
    MAX(CASE WHEN MONTH = 6 THEN count_of_stores END )  AS Count_of_Stores 
FROM monthGrouping
GROUP BY rn

edited Sep 18, 2018 at 16:17

answered Sep 18, 2018 at 13:41

GreyOrGray

1,73811 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tamil Over a year ago

Thanks! This looks good. this works for me. But What if i have different teams names or Id's. Say i have nine different team names or ID's for all the nine entries. In that case, is it possible to still split them and arrange? When i implement this logic, think i will be left with lot of NULLS values if i am not wrong. IS that right? what do you think?

GreyOrGray Over a year ago

Yes, you can be left with nulls if you are using different names or IDs. If you have different teams spanning across the months you could row_number() them by month and use the same type of data rotation by grouping off of that... similar to link. In order to give the best implementation advice, we really need to see an example of what the final results would be expected to look like.

Tamil Over a year ago

Team Month Count_of_Stores a 4 10 b 4 4 c 4 6 a 5 8 b 5 14 e 5 9 a 6 7 b 6 3 f 6 1

Tamil Over a year ago

And i would like to get theo/p like this Team Month Count_of_Stores Team Month Count_of_Stores Team Month Count_of_Stores a 4 10 a 5 8 a 6 7 b 4 4 b 5 14 b 6 3 c 4 6 e 5 9 f 6 1

Tamil Over a year ago

That's awesome! Worked like magic. Thanks a lot. And apologies for the confusion and incomplete data in the beginning.

Collectives™ on Stack Overflow

Converting multiple rows to multiple columns in SQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related