CONVERT COLUMN VALUES TO COMMA SEPARATED ONE ROW VALUE..

  • sql_learner29

    SSCrazy Eights

    Points: 8947

    hi all...

    I have a query the returns country....state...and cities

    the cities are creating ore rows than required, I need to re-write my query so that the cities column instead come in single row separated by commas...

    here is the data for more understanding

    CREATE TABLE [dbo].[test12](

    [title] [varchar](51) NULL,

    [subtitle] [varchar](52) NULL,

    [value] [varchar](53) NULL

    )

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'PA', 'PHILLY')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'PA', 'PITTSBURG')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'PA', 'WARREN')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'PA', 'UNION')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'EDISON')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'PARSIPPANY')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'METROPARK')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'PRINCTON')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'HAMILTON')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'ISELIN')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'PATERSON')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'PARAMUS')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'mClEAN')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'FAIRFAX')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'ARLINGTON')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'STAUNTON')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'NEWCITY')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'OLDCITY')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'MIAMI')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'TAMPA')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'ORLANDO')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'JACKSONVILLE')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'NEW')

    INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'OLD')

    select * from test12

    required output

    USA | PA | PHILLY,PITTSBURG,WARREN,UNION

    USA | NJ | EDISON,PARSIPPANY ,METROPARK , PRINCTON....

    USA | VA | mClEAN, FAIRFAX, ARLINGTON, STAUNTON...

    AND SO ON.............

    hope the requirement is clear...

    any help on this...

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • roryp 96873

    SSCertifiable

    Points: 6479

    ;with SampleDataR as

    (

    select *, ROW_NUMBER() over (partition by title, subtitle order by value) rownum

    from test12

    )

    select distinct title, subtitle,(

    select value

    + case when s1.rownum = (select MAX(rownum) from SampleDataR where title = s1.title and subtitle = s1.subtitle)

    then '' else ',' end from SampleDataR s1

    where s1.title = s2.title and s1.subtitle = s2.subtitle

    for xml path(''),type).value('(.)[1]','varchar(max)') csvList

    from SampleDataR s2

  • sql_learner29

    SSCrazy Eights

    Points: 8947

    Works like Charm...Thanks man roryp 96873...

    Thanks again

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    Similar to Rory's , but doenst require a ROW_NUMBER

    SELECT OutTab.title ,OutTab.[subtitle] ,

    Cities =

    STUFF ( ( SELECT ','+InrTab.value

    FROM [test12] InrTab

    WHERE InrTab.title = OutTab.title

    AND InrTab.subtitle = OutTab.subtitle

    ORDER BY InrTab.value

    FOR XML PATH(''),TYPE

    ).value('.','VARCHAR(MAX)')

    , 1,1,SPACE(0))

    FROM [test12] OutTab

    GROUP BY OutTab.title , OutTab.[subtitle] ;

  • ramakrishna-405526

    SSC Journeyman

    Points: 75

    Hi SSCrazy

    Your Query is very optimised and very fast,Thanks for the good post.

    It worked like a charm for me.

    Thanks

    rk

  • bornsql

    SSC-Addicted

    Points: 476

    USE AdventureWorks

    GO

    DECLARE @listStr VARCHAR(MAX)

    SELECT @listStr = COALESCE(@listStr+',' ,'') + Name

    FROM Production.Product

    SELECT @listStr

  • Jeff Moden

    SSC Guru

    Points: 993867

    bornsql (10/8/2012)


    USE AdventureWorks

    GO

    DECLARE @listStr VARCHAR(MAX)

    SELECT @listStr = COALESCE(@listStr+',' ,'') + Name

    FROM Production.Product

    SELECT @listStr

    How does that solve the problem the OP posted? It looks like it would put everything on a single row instead of by country and state.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Mnishar

    SSC Journeyman

    Points: 85

    Hi you can achieve this by XML

    Check this out

    http://www.sqlblogspot.com/2013/04/convert-rows-into-comma-separated.html

  • Jeff Moden

    SSC Guru

    Points: 993867

    Mnishar (7/4/2013)


    Hi you can achieve this by XML

    Check this out

    http://www.sqlblogspot.com/2013/04/convert-rows-into-comma-separated.html%5B/quote%5D

    Hi Nnishar,

    Consider using GROUP BY instead of DISTINCT. Here's your code with the WHERE clause removed to try to get the rowcounts up a bit. The first section uses DISTINCT and the second uses GROUP BY.

    PRINT '========== DISTINCT ====================================================';

    SET STATISTICS TIME,IO ON

    SELECT Distinct col2.table_name,

    Stuff((SELECT ',' + column_name

    -- Stuff used here only to strip the first character which is comma (,).

    FROM information_schema.columns col1

    WHERE col1.table_name = col2.table_name

    FOR xml path ('')), 1, 1, '')

    FROM information_schema.columns col2

    SET STATISTICS TIME,IO OFF

    GO

    PRINT '========== GROUP BY ====================================================';

    SET STATISTICS TIME,IO ON

    SELECT col2.table_name,

    Stuff((SELECT ',' + column_name

    -- Stuff used here only to strip the first character which is comma (,).

    FROM information_schema.columns col1

    WHERE col1.table_name = col2.table_name

    FOR xml path ('')), 1, 1, '')

    FROM information_schema.columns col2

    group by table_name

    SET STATISTICS TIME,IO OFF

    Here are the results from the "Messages" tab... you'll see the differences immediately.

    ========== DISTINCT ====================================================

    (30 row(s) affected)

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'syscolpars'. Scan count 31, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'sysschobjs'. Scan count 31, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 62 ms, elapsed time = 134 ms.

    ========== GROUP BY ====================================================

    (30 row(s) affected)

    Table 'syscolpars'. Scan count 31, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'sysschobjs'. Scan count 31, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 69 ms.

    The other thing to worry about is that XML will entitize (change to a code sequence) certain characters if they are present. It likely won't matter for the OP's problem but it certainly could for other applications of the technique. Here's one way to overcome that problem using TYPE along with the VALUE conversion.

    PRINT '========== GROUP BY DE-ENTITIZED =======================================';

    SET STATISTICS TIME,IO ON

    SELECT col2.table_name,

    Stuff((SELECT ',' + column_name

    -- Stuff used here only to strip the first character which is comma (,).

    FROM information_schema.columns col1

    WHERE col1.table_name = col2.table_name

    FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'')

    FROM information_schema.columns col2

    group by table_name

    SET STATISTICS TIME,IO OFF

    Unfortunately, the de-entitization process causes the code to use a fair bit more CPU (although it still beats DISTINCT by a fairly wide margin). For small stuff, that certainly won't seem like it matters but it will definitely matter on bigger stuff.

    ========== GROUP BY DE-ENTITIZED =======================================

    (30 row(s) affected)

    Table 'syscolpars'. Scan count 31, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'sysschobjs'. Scan count 31, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 15 ms, elapsed time = 18 ms.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Dwain Camps

    SSC Guru

    Points: 86873

    Jeff Moden (7/4/2013)


    The other thing to worry about is that XML will entitize (change to a code sequence) certain characters if they are present.

    I don't suppose you'd know where to get a list of those "certain characters?"

    I have searched before and come up empty, not knowing the proper googling terminology to use.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Sean Pearce

    SSCoach

    Points: 15750

    dwain.c (7/4/2013)


    Jeff Moden (7/4/2013)


    The other thing to worry about is that XML will entitize (change to a code sequence) certain characters if they are present.

    I don't suppose you'd know where to get a list of those "certain characters?"

    I have searched before and come up empty, not knowing the proper googling terminology to use.

    http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Dwain Camps

    SSC Guru

    Points: 86873

    Sean Pearce (7/5/2013)


    dwain.c (7/4/2013)


    Jeff Moden (7/4/2013)


    The other thing to worry about is that XML will entitize (change to a code sequence) certain characters if they are present.

    I don't suppose you'd know where to get a list of those "certain characters?"

    I have searched before and come up empty, not knowing the proper googling terminology to use.

    http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references

    How embarrasing! I'm in Wiki all the time. Guess I just didn't know what to look for.

    Thanks Sean!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • murthymamidim

    SSC Journeyman

    Points: 98

    Hi Rorys,

    Could u please explain the line which i didn't understood in the code which u give for the csvlist i.e

    ------value('(.)[1]','varchar(max)')-------

  • twin.devil

    SSC-Insane

    Points: 22208

    Check this

  • murthymamidim

    SSC Journeyman

    Points: 98

    Hi,

    Thanks for giving reply ...

    Thanks,

    Murthy

Viewing 15 posts - 1 through 15 (of 18 total)

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