Turning Col into Multiple Rows

Question

Post reply

Turning Col into Multiple Rows

rjjh78

SSCrazy

Points: 2784

August 18, 2022 at 5:40 pm

#4078050

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!

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

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

DesNorton SSC-Insane Points: 24279 More actions · Answer 1

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

How to post data/code on a forum to get the best help.

rjjh78 SSCrazy Points: 2784 More actions · Answer 2

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.

DesNorton SSC-Insane Points: 24279 More actions · Answer 3

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

How to post data/code on a forum to get the best help.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 4

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 will give you the shirt off *someone else's* back.

jschmidt 17654 SSCommitted Points: 1610 More actions · Answer 5

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;

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 6

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)

sgmunson SSC Guru Points: 110639 More actions · Answer 7

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 year, 8 months ago by sgmunson. Reason: Results got fouled up in formatting

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)