Converting multiple rows to multiple columns in SQL

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.

1 Answer
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


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy