Turning Col into Multiple Rows

  • Hello,

    I am interested in turning one column into multiple rows depending on the value.  I tried a pivot but seem to be doing something wrong.  Here is some sample code with desired results following:

     

    -- DROP TABLE #t

    CREATE TABLE #t (ID int IDENTITY(1,1), CoID int, CoName varchar(100), Class varchar(100))
    INSERT INTO #t (CoID, Coname, Class) VALUES (101, 'Apple', 'aaa')
    INSERT INTO #t (CoID, Coname, Class) VALUES (101, 'Apple', 'zzz')
    INSERT INTO #t (CoID, Coname, Class) VALUES (102, 'Netflix', 'aaa')
    INSERT INTO #t (CoID, Coname, Class) VALUES (103, 'Google', 'aaa')
    INSERT INTO #t (CoID, Coname, Class) VALUES (104, 'Amazon', 'zzz')
    INSERT INTO #t (CoID, Coname, Class) VALUES (105, 'Microsoft', 'zzz')
    INSERT INTO #t (CoID, Coname, Class) VALUES (106, 'AMC', 'zzz')
    INSERT INTO #t (CoID, Coname, Class) VALUES (107, 'Carnival', 'abc')
    INSERT INTO #t (CoID, Coname, Class) VALUES (107, 'Carnival', 'zzz')
    INSERT INTO #t (CoID, Coname, Class) VALUES (108, 'BTC', 'abc')
    INSERT INTO #t (CoID, Coname, Class) VALUES (109, 'XRP', 'abc')

    -- SELECT * FROM #t

    Desired output:

    Thank you in advance!

     

     

     

  • This SQL will achieve the required results

    SELECT Company = src.CoName
    , aaa = CASE WHEN src.Class = 'aaa' THEN 'Yes' ELSE '' END
    , zzz = CASE WHEN src.Class = 'zzz' THEN 'Yes' ELSE '' END
    , abc = CASE WHEN src.Class = 'abc' THEN 'Yes' ELSE '' END
    FROM #t AS src
  • Many thanks for the quick reply!

    The trouble with the case solution is that I still get mutliple rows per company.  What I am hoping to do is consolidate each company to one row and show their classes.

  • Your sample data did not contain any duplicate companies.

    To cater for duplicates, wrap each CASE ... END statement in a MAX(CASE ... END)

    and add a GROUP BY src.CoName

  •  

    SELECT 
    CoName,
    MAX(CASE WHEN Class = 'aaa' THEN 'Yes' ELSE '' END) AS aaa,
    MAX(CASE WHEN Class = 'zzz' THEN 'Yes' ELSE '' END) AS zzz,
    MAX(CASE WHEN Class = 'abc' THEN 'Yes' ELSE '' END) AS abc
    FROM #t
    GROUP BY CoName
    ORDER BY CoName

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • I attempted some dynamic code with the thought that Class might be more than the 3 values in sample data. To get an empty string instead of a null, I used a second list of the Class column variables with ISNULL as seen in an example by LutzM

    There might be better ways to do it, but this was an interesting memory recall with help from previous SLQ Server Central questions.

    DECLARE @cols AS NVARCHAR(MAX),
    @cols_null_handler AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX)


    select @cols = STUFF((SELECT ',' + QUOTENAME(Class)
    from #t
    group by Class
    order by Class
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

    select @cols_null_handler = STUFF((SELECT ',ISNULL(' + QUOTENAME(Class) + ','''') AS '+ QUOTENAME(Class)
    from #t
    group by Class
    order by Class
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

    set @query = N'SELECT CoID, CoName, ' + @cols_null_handler + N' from
    (
    select CoID, CoName, Class, value=''Yes''
    from #t
    ) x
    pivot
    (
    max(x.value)
    for Class in (' + @cols + N')
    ) p
    order by CoID'

    exec sp_executesql @query;
  • Have a look at the following article to support such "Dynamic CrossTabs".  Use MAX() instead of SUM() like Scott Pletcher did.  With the understanding that I have little love for the PIVOT operator, the Dynamic PIVOT code that jschmidt 17654 posted also looks like it'll work.

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I just noticed that the companies are ordered in the "desired results" by the CoID.   To make that happen, here's the code, including the supplied table creation and data insert script:

    CREATE TABLE #t (
    ID int IDENTITY(1,1),
    CoID int,
    CoName varchar(100),
    Class varchar(100)
    );
    INSERT INTO #t (CoID, Coname, Class) VALUES (101, 'Apple', 'aaa');
    INSERT INTO #t (CoID, Coname, Class) VALUES (101, 'Apple', 'zzz');
    INSERT INTO #t (CoID, Coname, Class) VALUES (102, 'Netflix', 'aaa');
    INSERT INTO #t (CoID, Coname, Class) VALUES (103, 'Google', 'aaa');
    INSERT INTO #t (CoID, Coname, Class) VALUES (104, 'Amazon', 'zzz');
    INSERT INTO #t (CoID, Coname, Class) VALUES (105, 'Microsoft', 'zzz');
    INSERT INTO #t (CoID, Coname, Class) VALUES (106, 'AMC', 'zzz');
    INSERT INTO #t (CoID, Coname, Class) VALUES (107, 'Carnival', 'abc');
    INSERT INTO #t (CoID, Coname, Class) VALUES (107, 'Carnival', 'zzz');
    INSERT INTO #t (CoID, Coname, Class) VALUES (108, 'BTC', 'abc');
    INSERT INTO #t (CoID, Coname, Class) VALUES (109, 'XRP', 'abc');

    WITH GROUPED_DATA AS (

    SELECT
    T.CoName,
    MAX(CASE T.Class WHEN 'aaa' THEN 'Yes' ELSE '' END) AS aaa,
    MAX(CASE T.Class WHEN 'zzz' THEN 'Yes' ELSE '' END) AS zzz,
    MAX(CASE T.Class WHEN 'abc' THEN 'Yes' ELSE '' END) AS abc
    FROM #t AS T
    GROUP BY T.CoName
    )
    SELECT
    GD.CoName,
    GD.aaa,
    GD.zzz,
    GD.abc
    FROM GROUPED_DATA AS GD
    CROSS APPLY (
    SELECT DISTINCT T1.CoName, T1.CoID
    FROM #t AS T1
    WHERE T1.CoName = GD.CoName
    ) AS T
    ORDER BY T.CoId;

    DROP TABLE IF EXISTS #t;

    The results:

    CoName     aaa  zzz  abc
    ---------- ---- ---- ----
    Apple Yes Yes
    Netflix Yes
    Google Yes
    Amazon Yes
    Microsoft Yes
    AMC Yes
    Carnival Yes Yes
    BTC Yes
    XRP Yes

    • This reply was modified 1 month ago by  sgmunson. Reason: Results got fouled up in formatting

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply