Case statement in a un pivot scenario sql

Question

I'm trying to un pivot a query set as below

Member ID   N1code  N2code  N3code  Name 
1234        234     567     878     ted 
23344       4242    23232           Mike

I want a case statement of some kind that helps show the type after unpivoting

MemberID Codes  Name    Type 
1234     234    ted     N1
1234     567    ted     N2
1234     878    ted     N3
23344    4242   Mike    N1
23344   23232   Mike    N2

Possible duplicate of [Unpivot with column name](https://stackoverflow.com/questions/19055902/unpivot-with-column-name) — Tab Alleman, Jul 18 '18 at 18:55

score 0 · Answer 1 · answered Jul 18 '18 at 18:55

0

in PostgreSQL you could turn the columns to an array and then unnest (explode):

select MemberID, unnest(array[N1code, N2code, N3code]) as Codes, Name
from table

answered Jul 18 '18 at 18:55

moshevi

4,999
5
33
50

`Type` is coming from the `N(x)code` column names, with only the first 2 characters – Tab Alleman Jul 18 '18 at 18:58
i edited it out because it should be a comment on the question and not in the answer – moshevi Jul 18 '18 at 19:00

Shawn · Answer 2 · 2018-07-18T19:28:56.400

In SQL Server, it's just a simple UNPIVOT. There's no need for the CASE statement, as the original column name comes back in the UNPIVOT. We're able to just pull the left 2 characters from the original column name. If you wanted it to be something other than a derivative of the column name, you would have to use a CASE.

Look at the outermost select to see where those selected columns are coming from.

SELECT unpiv.MemberID, unpiv.Name, unpiv.theCode, left(codes,2) AS theType
FROM
(
  SELECT MemberID, Name, N1Code, N2Code, N3Code
  FROM t1
) AS src
UNPIVOT 
(
  theCode FOR codes IN (N1Code, N2Code, N3Code)
) AS unpiv;

For MySQL 5.x and lower, you can use a CROSS JOIN and a couple of CASE statements.

SELECT t1.MemberID, t1.Name
    , CASE cj.colName
        WHEN 'N1Code' then t1.N1Code
        WHEN 'N2Code' then t1.N2Code
        WHEN 'N3Code' then t1.N3Code
      END AS theCode
    , CASE cj.colName
        WHEN 'N1Code' then 'N1'
        WHEN 'N2Code' then 'N2'
        WHEN 'N3Code' then 'N3'
      END AS theType
FROM t1
CROSS JOIN
(
  SELECT 'N1Code' AS colName
  UNION ALL
  SELECT 'N2Code'
  UNION ALL
  SELECT 'N3Code'
) cj

Thank you ! i did resolve it with the Cross apply function. – k Drogon Jul 27 '18 at 15:07 — k Drogon, Jul 27 '18 at 15:07

Case statement in a un pivot scenario sql

2 Answers2