CODEWITHSUNDEEP
sqlsql-server
Dungeon
Dungeon

Reputation: 1040

How to separate rows into columns in SQL based on certain condition?

I have data like this:

Year    Q1      Q2      Q3      Q4
------------------------------------
2000    421     123     145     126
2000    422     124     146     127
2000    423     125     147     128
2000    424     126     148     129
2001    425     127     149     130
2001    426     128     150     131
2001    427     129     151     132
2002    428     130     152     133
2002    429     131     153     134

How do I get it like this:

Year    Q1      Q2      Q3      Q4      Year    Q1      Q2      Q3      Q4      Year    Q1  Q2  Q3  Q4
--------------------------------------------------------------------------------------------------------
2000    421     123     145     126     2001    425     127     149     130     2002    428 130 152 133
2000    422     124     146     127     2001    426     128     150     131     2002    429 131 153 134
2000    423     125     147     128     2001    427     129     151     132                 
2000    424     126     148     129

I visited this:https://www.databasejournal.com/features/mssql/converting-rows-to-columns-pivot-and-columns-to-rows-unpivot-in-sql-server.html but no success. May I can do that with PIVOT But I am not been able to do that. Please suggest me. Thanks in Advance.

Upvotes: 0

Views: 51

Answers (1)

Thom A
Thom A

Reputation: 95620

This is an ugly solution, in all honesty, and I don't really know why you'd want data to look like this. The ORDER BY in the OVER clause is a total guess, mind:

WITH VTE AS(
    SELECT *
    FROM (VALUES(2000,421,123,145,126),
                (2000,422,124,146,127),
                (2000,423,125,147,128),
                (2000,424,126,148,129),
                (2001,425,127,149,130),
                (2001,426,128,150,131),
                (2001,427,129,151,132),
                (2002,428,130,152,133),
                (2002,429,131,153,134)) V ([Year], Q1, Q2, Q3, Q4)),
RNs AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY Q1) AS RN --ORDER BY is total guess
    FROM VTE)
SELECT RN1.[Year], RN1.Q1, RN1.Q2, RN1.Q3, RN1.Q4,
       RN2.[Year], RN2.Q1, RN2.Q2, RN2.Q3, RN2.Q4,
       RN3.[Year], RN3.Q1, RN3.Q2, RN3.Q3, RN3.Q4
FROM RNs RN1
     LEFT JOIN RNs RN2 ON RN1.[Year] = RN2.[Year] - 1
                      AND RN1.RN = RN2.RN
     LEFT JOIN RNs RN3 ON RN1.[Year] = RN3.[Year] - 2
                      AND RN1.RN = RN3.RN
WHERE RN1.[Year] = (SELECT MIN(sq.[Year])
                    FROM RNs sq)
ORDER BY RN1.RN;

This will also not work as intended if a year after the lowest has more rows that the first one (for example, if 2003 had 5 rows, you would only get 4). This could be handled, if really needed.

Edit: IT's still ugly, but this helps when you have more row later on:

WITH VTE AS(
    SELECT *
    FROM (VALUES(2000,421,123,145,126),
                (2000,422,124,146,127),
                (2000,423,125,147,128),
                (2000,424,126,148,129),
                (2001,425,127,149,130),
                (2001,426,128,150,131),
                (2001,427,129,151,132),
                (2002,428,130,152,133),
                (2002,429,131,153,134),
                (2003,429,132,154,135),
                (2003,430,133,155,136),
                (2003,431,134,156,137),
                (2003,432,135,157,138),
                (2003,433,136,158,139)) V ([Year], Q1, Q2, Q3, Q4)),
RNs AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY Q1) AS RN --ORDER BY is total guess
    FROM VTE),
Tally AS (
    SELECT DISTINCT RNs.RN, Y.MinYear
    FROM RNs
         CROSS APPLY (SELECT MIN(Year) AS MinYear FROM RNs) Y)
SELECT RN1.[Year], RN1.Q1, RN1.Q2, RN1.Q3, RN1.Q4,
       RN2.[Year], RN2.Q1, RN2.Q2, RN2.Q3, RN2.Q4,
       RN3.[Year], RN3.Q1, RN3.Q2, RN3.Q3, RN3.Q4,
       RN4.[Year], RN4.Q1, RN4.Q2, RN4.Q3, RN4.Q4
FROM Tally T
     LEFT JOIN RNs RN1 ON T.RN = RN1.RN
                      AND RN1.[Year] = T.MinYear
     LEFT JOIN RNs RN2 ON T.MinYear = RN2.[Year] - 1
                      AND T.RN = RN2.RN
     LEFT JOIN RNs RN3 ON T.MinYear = RN3.[Year] - 2
                      AND T.RN = RN3.RN
     LEFT JOIN RNs RN4 ON T.MinYear = RN4.[Year] - 3
                      AND T.RN = RN4.RN
ORDER BY T.RN;

Upvotes: 1

Related Questions