How can I use SQL Pivot Table to turn my rows of data into columns

Question

In SQL 2008, I need to flatten a table and show extra rows as columns. All I can find are queries with calculations. I just want to show the raw data. The data is like as below (simplified):

ID#     Name       Name_Type
1       Mary Jane  Legal
1       MJ         Nickname
1       Smith      Maiden
2       John       Legal
3       Suzanne    Legal
3       Susie      Nickname

I want the data to show as:

ID#     Legal      Nickname      Maiden
1       Mary Jane  MJ            Smith
2       John
3       Suzanne    Susie

where nothing shows in the column if there is not a row existing for that column. I'm thinking the Pivot Table method should work.

There are 1000000 examples of using PIVOT what exactly did you try? — Hogan, Nov 27 '19 at 21:31
Does this answer your question? [TSQL Pivot without aggregate function](https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function) — DeanOC, Nov 27 '19 at 22:06

score 0 · Answer 1 · answered Nov 27 '19 at 22:00

PIVOT requires you to use an aggregate. See this post for a better explanation of how it works.

CREATE TABLE #MyTable
(
    ID# INT
    , Name VARCHAR(50)
    , Name_Type VARCHAR(50)
);

INSERT INTO #MyTable VALUES
(1, 'Mary Jane', 'Legal')
, (1, 'MJ', 'Nickname')
, (1, 'Smith', 'Maiden')
, (2, 'John', 'Legal')
, (3, 'Suzanne', 'Legal')
, (3, 'Susie', 'Nickname');

SELECT * 
FROM
    (
        SELECT * FROM #MyTable
    ) AS Names
    PIVOT (MAX(NAME)
    FOR Name_Type IN ([Legal], [Nickname], [Maiden]))
    AS PVT;

DROP TABLE #MyTable;

Matthias Gwiozda · Answer 2 · 2019-11-28T21:33:49.730

0

Try this (replace "new_Table" with your table - name and "ID_" with your id - column):

SELECT ID_ AS rootID, (
SELECT Name
    FROM new_table
        WHERE Name_type = 'legal'
            AND new_table.ID_ = rootID
) AS legal,
(
SELECT Name
    FROM new_table
        WHERE Name_type = 'Nickname'
            AND ID_ = rootID
) AS Nickname,
(
SELECT Name
    FROM new_table
        WHERE Name_type = 'Maiden'
            AND ID_ = rootID
) AS Maiden
FROM new_table
    GROUP BY rootID;

edited Nov 28 '19 at 21:33

answered Nov 27 '19 at 22:18

Matthias Gwiozda

505
5
14

Many good suggestions here. I will try a few. There seem to be many ways to flatten the table. Thanks so much. Appreciated as I am not a programmer but have a very short deadline to extract data from 5 different tables with several one to many relationships that all have to go into a single flat file. After 5 hours of unsuccessful searching, time was running out. Thank you for tolerating my redundant question as time was of the essence. – Kelly Nov 28 '19 at 19:47
Did you try the query ? – Matthias Gwiozda Nov 29 '19 at 22:24

How can I use SQL Pivot Table to turn my rows of data into columns

2 Answers2