Column listing in SQL

Question

Input:

Name         Id

N1            1
N1            3
N1            4
N1            7
N2            2
N2            1
N2            8
N2            5
N3            4
N3            8
N3            5
N3            3
N4            7
N4            7
N4            7
N4            8

Output:

Name1 Name2 Name3 Name4
-----------------------
  N1   N2   N3      N4
   1    2    4      7 
   3    1    8      7
   4    8    5      7
   7    5    3      8

Help please

Answer 1

You can't do that because according to the microsoft help documents:

::= ( aggregate_function ( value_column ) FOR pivot_column IN ( ) )

So unless you are doing some sort of aggregate function you aren't going to just get a pivot of the column using the pivot function.

However, you can do a pivot of the data above with a normal select and a case statement.

Answer 2

Select N1, N2, N3, N4
from
(select N_Column, N_Values from [your_table]) p
PIVOT MIN(Tokens)
FOR NameList IN ([N1],[N2],[N3],[N4])
) pvt

I called the columns N_column and N_Values from your original table [your_table] Not sure of the actual names.

If you use min, max you can aggregate any value since there is only one.

Answer 3

It's not pretty, but I've used a case statement for this, but the headings will need to be predetermined.

SELECT CASE heading
WHEN 'N1' THEN value END AS 'N1'
, CASE heading WHEN 'N2' THEN value END AS 'N2'
, CASE heading WHEN 'N3' THEN value END AS 'N3'
, CASE heading WHEN 'N4' THEN value END AS 'N4'

Answer 4

This is called EAV design. If you expect large amounts of data, your performance will be very poor. If that's the case you may want to switch to a table with N1 N2 N3 N4 as columns, abandoning your current design.

Answer 5

I don't think your problem is well-defined, but here's a solution:

--CREATE TABLE so1008354 (
--  Name1 VARCHAR(10)
--  ,Name2 VARCHAR(10)
--  ,Name3 VARCHAR(10)
--  ,Name4 VARCHAR(10)
--)
--
--INSERT INTO so1008354 (Name1, Name2, Name3, Name4) VALUES ( 'N1', 'N2', 'N3', 'N4' ) 
--INSERT INTO so1008354 (Name1, Name2, Name3, Name4) VALUES ( '1', '2', '4', '7' ) 
--INSERT INTO so1008354 (Name1, Name2, Name3, Name4) VALUES ( '3', '1', '8', '7' ) 
--INSERT INTO so1008354 (Name1, Name2, Name3, Name4) VALUES ( '4', '8', '5', '7' ) 
--INSERT INTO so1008354 (Name1, Name2, Name3, Name4) VALUES ( '7', '5', '3', '8' ) 

SELECT  *
FROM    so1008354

--
;
WITH    unpvt
          AS ( SELECT   col1,
                        col2
               FROM     so1008354 UNPIVOT ( col2 FOR col1 IN ( [Name1], [Name2], [Name3], [Name4] ) ) AS unpvt
             ) ,
        XLate
          AS ( SELECT   col1 AS NameList_pre,
                        col2 AS NameList
               FROM     unpvt
               WHERE    col2 LIKE 'N%'
             ) ,
        Data
          AS ( SELECT   col1 AS NameList_pre,
                        col2 AS Tokens
               FROM     unpvt
               WHERE    col2 NOT LIKE 'N%'
             )
    SELECT  XLate.NameList,
            Data.Tokens
    FROM    Data
            INNER JOIN XLate ON XLate.NameList_pre = Data.NameList_pre
    ORDER BY XLate.NameList,
            Data.Tokens

Which gives results:

Name1      Name2      Name3      Name4
---------- ---------- ---------- ----------
N1         N2         N3         N4
1          2          4          7
3          1          8          7
4          8          5          7
7          5          3          8

(5 row(s) affected)

NameList   Tokens
---------- ----------
N1         1
N1         3
N1         4
N1         7
N2         1
N2         2
N2         5
N2         8
N3         3
N3         4
N3         5
N3         8
N4         7
N4         7
N4         7
N4         8

(16 row(s) affected)

Answer 6

select 'N1' , Name1 from your_able where name1 <> 'N1' union

select 'N2' , Name2 from your_able where name2 <> 'N2'

union

select 'N3' , Name3 from your_able where name3 <> 'N3'

union

select 'N4' , Name4 from your_able where name4 <> 'N4'

-- the table is not normalized hence this kind of sql is used.

Answer 7

This works but only if N1...N4 all have the same number of Tokens.

SELECT 'N1' AS 'Name1', 'N2' AS 'Name2', 'N3' AS 'Name3', 'N4' AS 'Name4'

UNION ALL

SELECT 
        CAST(t1.Tokens AS varchar), 
        CAST(t2.Tokens AS varchar), 
        CAST(t3.Tokens AS varchar), 
        CAST(t4.Tokens AS varchar)

     FROM (

        (SELECT Tokens, ROW_NUMBER() OVER(ORDER BY Tokens) AS rownum FROM so2 WHERE NameList = 'N1') t1
         JOIN (SELECT Tokens, ROW_NUMBER() OVER(ORDER BY Tokens) AS rownum FROM so2 WHERE NameList = 'N2') t2 ON t1.rownum = t2.rownum
         JOIN (SELECT Tokens, ROW_NUMBER() OVER(ORDER BY Tokens) AS rownum FROM so2 WHERE NameList = 'N3') t3 ON t1.rownum = t3.rownum
         JOIN (SELECT Tokens, ROW_NUMBER() OVER(ORDER BY Tokens) AS rownum FROM so2 WHERE NameList = 'N4') t4 ON t1.rownum = t4.rownum
    )