How to use stuff function in this case

  • gautham.gn

    Mr or Mrs. 500

    Points: 530

    I have come across this and I felt a little difficulty in doing this. please go through this once.

    create table example

    ( a int null,

    b varchar(100) null,

    c varchar(100) null,

    d int null)

    insert into example

    select 220,'abc','yes',1 union all

    select 220,'abc','yes',2 union all

    select 220,'abc','yes',3 union all

    select 220,'abc','yes',4 union all

    select 220,'abc','no',132 union all

    select 220,'abc','no',23 union all

    select 220,'abc','no',34 union all

    select 220,'abc','no',43 union all

    select 243,'raju','NA',123 union all

    select 243,'raju','NA',456 union all

    select 243,'raju','NA',789 union all

    select 243,'raju','Reg',21 union all

    select 243,'raju','Reg',23 union all

    select 243,'raju','Reg',12

    I want to display the result as

    220,'abc', 'yes(1,2,3,4), no(132,23,34,43)'

    243,'raju', 'NA(123,456,789), Reg(21,23,12)'

  • gautham.gn

    Mr or Mrs. 500

    Points: 530

    Can u please help me with this?

    Thanks

    Gautham

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714067

    Happy to help, but this really looks like a homework issue, and we prefer to help you once you've tried something, rather than giving you some answer.

    A few hints. First, you'll need to tackle this by getting the row values into a string. You can do something like:

    DECLARE @response VARCHAR(8000)

    SELECT @response = @response + ', ' + d

    FROM example

    Use that, with some concatenation to get the string of responses. Then you'll probably make that a CTE or subquery you join back to the regular table with some distinct other values.

  • gautham.gn

    Mr or Mrs. 500

    Points: 530

    I have come across this and I felt a little difficulty in doing this. please go through this once.

    create table example

    ( a int null,

    b varchar(100) null,

    c varchar(100) null,

    d varchar(1000) null)

    insert into example

    select 220,'abc','yes',1 union all

    select 220,'abc','yes',2 union all

    select 220,'abc','yes',3 union all

    select 220,'abc','yes',4 union all

    select 220,'abc','no',132 union all

    select 220,'abc','no',23 union all

    select 220,'abc','no',34 union all

    select 220,'abc','no',43 union all

    select 243,'raju','NA',123 union all

    select 243,'raju','NA',456 union all

    select 243,'raju','NA',789 union all

    select 243,'raju','Reg',21 union all

    select 243,'raju','Reg',23 union all

    select 243,'raju','Reg',12

    I want to display the result as

    220,'abc', 'yes(1,2,3,4), no(132,23,34,43)'

    243,'raju', 'NA(123,456,789), Reg(21,23,12)'

    Please correct the datatype of column d as varchar(1000).

    I also tried using the below query. but I m not getting the actual result.

    select a,b, stuff(

    (select ', '+lovs from

    (select a,b,c,c+'('+

    stuff((select ', '+d from example with (nolock)

    where a = x.a and b = x.b and c=x.c for xml path(''))+')',1,1,'')

    as lovs from example as x with (nolock) group by a,b,c)

    y

    where a = y.a and b = y.b

    for xml path('')),1,1,'')

    from example group by a,b

  • Jeff Moden

    SSC Guru

    Points: 993628

    What has become a classic method for doing this may be found at the following URL...

    http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    --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

  • Jeff Moden

    SSC Guru

    Points: 993628

    It's late and I'm out of coffee so I might not be thinking clearly. This does 1 + x + y table scans where "x" is the

    unique number of combinations of a/b and y is the number of unique values in in the "c" column, which is absolutely terrible. Someone with a better grip on FOR XML PATH might be able to pull something a little better off.

    Still, the following code does work.

    WITH

    ctePreAgg AS

    (

    SELECT eo.a

    ,eo.b

    --,c_Unique = eo.c --Don't need this anymore

    ,d_CSV = STUFF(

    (

    SELECT ','+CAST(ei.d AS VARCHAR(10))

    FROM dbo.example ei

    WHERE ei.a = eo.a

    AND ei.b = eo.b

    AND ei.c = eo.c

    ORDER BY ei.d

    FOR XML PATH('')

    )

    ,1,1,eo.c+'(')

    + ')'

    FROM dbo.Example eo

    GROUP BY eo.a,eo.b,eo.c

    )

    SELECT pao.a

    ,pao.b

    ,Final_CSV = STUFF(

    (

    SELECT ','+pai.d_CSV

    FROM ctePreAgg pai

    WHERE pai.a = pao.a

    AND pai.b = pao.b

    ORDER BY pai.d_CSV

    FOR XML PATH('')

    )

    ,1,1,'')

    FROM ctePreAgg pao

    GROUP BY pao.a, pao.b

    ;

    Using the cte code to populate a temp table instead of popping the cte twice cuts the reads to 1/3rd of the cte without doing much for cpu, but only if you add a clustered index on a,b,c. Like this...

    SELECT eo.a

    ,eo.b

    --,c_Unique = eo.c --Don't need this anymore

    ,d_CSV = STUFF(

    (

    SELECT ','+CAST(ei.d AS VARCHAR(10))

    FROM dbo.example ei

    WHERE ei.a = eo.a

    AND ei.b = eo.b

    AND ei.c = eo.c

    ORDER BY ei.d

    FOR XML PATH('')

    )

    ,1,1,eo.c+'(')

    + ')'

    INTO #PreAgg

    FROM dbo.Example eo

    GROUP BY eo.a,eo.b,eo.c

    SELECT pao.a

    ,pao.b

    ,Final_CSV = STUFF(

    (

    SELECT ','+pai.d_CSV

    FROM #PreAgg pai

    WHERE pai.a = pao.a

    AND pai.b = pao.b

    ORDER BY pai.d_CSV

    FOR XML PATH('')

    )

    ,1,1,'')

    FROM #PreAgg pao

    GROUP BY pao.a, pao.b

    ;

    Like I said, it's late and I hope someone else can come up with a better solution.

    --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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    Quick solution and an opposite problem to Jeff's, lots of coffee but yet to be drunken.

    😎

    USE tempdb;

    GO

    IF (SELECT OBJECT_ID(N'dbo.example')) IS NOT NULL DROP TABLE dbo.example;

    create table dbo.example

    ( a int null,

    b varchar(100) null,

    c varchar(100) null,

    d varchar(1000) null);

    insert into dbo.example

    select 220,'abc','yes',1 union all

    select 220,'abc','yes',2 union all

    select 220,'abc','yes',3 union all

    select 220,'abc','yes',4 union all

    select 220,'abc','no',132 union all

    select 220,'abc','no',23 union all

    select 220,'abc','no',34 union all

    select 220,'abc','no',43 union all

    select 243,'raju','NA',123 union all

    select 243,'raju','NA',456 union all

    select 243,'raju','NA',789 union all

    select 243,'raju','Reg',21 union all

    select 243,'raju','Reg',23 union all

    select 243,'raju','Reg',12 ;

    ;WITH VALUE_LIST AS

    (

    SELECT DISTINCT

    E.a

    ,E.b

    ,E.c

    ,E.c + '(' + STUFF((SELECT ',' + EX.d

    FROM dbo.example EX

    WHERE E.a = EX.A

    AND E.c = EX.C

    FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(MAX)'),1,1,'') + ')' AS PAARTS

    FROM dbo.example E

    )

    SELECT DISTINCT

    VL.a

    ,VL.b

    ,STUFF((SELECT ', ' + VX.PAARTS

    FROM VALUE_LIST VX

    WHERE VL.a = VX.a

    FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(MAX)'),1,2,'') AS CONCAT_VAL

    FROM VALUE_LIST VL;

    Results

    a b CONCAT_VAL

    ----- ------ -------------------------------

    220 abc no(132,23,34,43), yes(1,2,3,4)

    243 raju NA(123,456,789), Reg(21,23,12)

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    Another quick solution, less flexibility but only a single table scan

    😎

    USE tempdb;

    GO

    IF (SELECT OBJECT_ID(N'dbo.example')) IS NOT NULL DROP TABLE dbo.example;

    create table dbo.example

    ( a int null,

    b varchar(100) null,

    c varchar(100) null,

    d varchar(1000) null);

    insert into dbo.example

    select 220,'abc','yes',1 union all

    select 220,'abc','yes',2 union all

    select 220,'abc','yes',3 union all

    select 220,'abc','yes',4 union all

    select 220,'abc','no',132 union all

    select 220,'abc','no',23 union all

    select 220,'abc','no',34 union all

    select 220,'abc','no',43 union all

    select 243,'raju','NA',123 union all

    select 243,'raju','NA',456 union all

    select 243,'raju','NA',789 union all

    select 243,'raju','Reg',21 union all

    select 243,'raju','Reg',23 union all

    select 243,'raju','Reg',12 ;

    ;WITH BASE_DATA AS

    (

    SELECT

    E.a

    ,E.b

    ,ROW_NUMBER() OVER

    (

    PARTITION BY E.a

    ,E.c

    ORDER BY E.d

    ) AS E_RID

    ,E.c

    ,E.d

    FROM dbo.example E

    )

    ,CONCAT_BASE AS

    (

    SELECT

    BD.a

    ,BD.b

    ,CONCAT(BD.c, '('

    ,ISNULL( MAX(CASE WHEN BD.E_RID = 1 THEN BD.d END),'')

    ,ISNULL(',' + MAX(CASE WHEN BD.E_RID = 2 THEN BD.d END),'')

    ,ISNULL(',' + MAX(CASE WHEN BD.E_RID = 3 THEN BD.d END),'')

    ,ISNULL(',' + MAX(CASE WHEN BD.E_RID = 4 THEN BD.d END),'')

    ,ISNULL(',' + MAX(CASE WHEN BD.E_RID = 5 THEN BD.d END),'')

    ,ISNULL(',' + MAX(CASE WHEN BD.E_RID = 6 THEN BD.d END),''),')') AS B_VAL

    FROM BASE_DATA BD

    GROUP BY BD.a

    ,BD.b

    ,BD.c

    )

    ,GROUPED_VALUES AS

    (

    SELECT

    CB.a

    ,CB.b

    ,ROW_NUMBER() OVER (PARTITION BY CB.a ORDER BY (SELECT NULL)) AS CB_RID

    ,CB.B_VAL

    FROM CONCAT_BASE CB

    )

    SELECT

    GV.a

    ,GV.b

    ,CONCAT

    (

    MAX(CASE WHEN GV.CB_RID = 1 THEN GV.B_VAL END)

    ,ISNULL(', ' + MAX(CASE WHEN GV.CB_RID = 2 THEN GV.B_VAL END),'')

    ,ISNULL(', ' + MAX(CASE WHEN GV.CB_RID = 3 THEN GV.B_VAL END),'')

    ,ISNULL(', ' + MAX(CASE WHEN GV.CB_RID = 4 THEN GV.B_VAL END),'')

    ,ISNULL(', ' + MAX(CASE WHEN GV.CB_RID = 5 THEN GV.B_VAL END),'')

    ,ISNULL(', ' + MAX(CASE WHEN GV.CB_RID = 6 THEN GV.B_VAL END),'')

    ) AS CONC_VAL

    FROM GROUPED_VALUES GV

    GROUP BY GV.a,GV.b;

    Results

    a b CONC_VAL

    ----- ------ -------------------------------

    220 abc no(132,23,34,43), yes(1,2,3,4)

    243 raju NA(123,456,789), Reg(12,21,23)

  • gautham.gn

    Mr or Mrs. 500

    Points: 530

    Thanks everyone.

    But Can we achieve the result without cte.

    I want to use stuff inside stuff.

    And I achieved it guys.

    Thanks for supporting me guys..

  • Jeff Moden

    SSC Guru

    Points: 993628

    gautham.gn (10/14/2014)


    Thanks everyone.

    But Can we achieve the result without cte.

    I want to use stuff inside stuff.

    And I achieved it guys.

    Thanks for supporting me guys..

    Great. Glad you achieved what you wanted. But it's a two way street here. Please post your code so that we can learn what you did to avoid CTEs for this problem and the reason why you wanted to avoid CTEs to begin with. Thanks.

    --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

  • gautham.gn

    Mr or Mrs. 500

    Points: 530

    Hi Guys,

    This is how I achieved the result.

    select a,b, stuff(

    (select ', '+level1 from

    (select a,b,c+'('+

    stuff((select ', '+d from example with (nolock)

    where a = x.a and b = x.b and c=x.c for xml path(''))+')',1,1,'')

    as level1 from example as x with (nolock) group by a,b,c) y

    where a = z.a and b = z.b

    for xml path('')),1,1,'') as level2

    from

    (select a,b from example with (nolock) group by a,b)z

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    Quick question, why are you using the nolock hint?

    😎

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714067

  • Jeff Moden

    SSC Guru

    Points: 993628

    gautham.gn (10/15/2014)


    Hi Guys,

    This is how I achieved the result.

    select a,b, stuff(

    (select ', '+level1 from

    (select a,b,c+'('+

    stuff((select ', '+d from example with (nolock)

    where a = x.a and b = x.b and c=x.c for xml path(''))+')',1,1,'')

    as level1 from example as x with (nolock) group by a,b,c) y

    where a = z.a and b = z.b

    for xml path('')),1,1,'') as level2

    from

    (select a,b from example with (nolock) group by a,b)z

    Thanks for posting that though I'm still curious why you considered it to be important to avoid CTEs.

    --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

  • Jeff Moden

    SSC Guru

    Points: 993628

    gautham.gn (10/15/2014)


    Hi Guys,

    This is how I achieved the result.

    select a,b, stuff(

    (select ', '+level1 from

    (select a,b,c+'('+

    stuff((select ', '+d from example with (nolock)

    where a = x.a and b = x.b and c=x.c for xml path(''))+')',1,1,'')

    as level1 from example as x with (nolock) group by a,b,c) y

    where a = z.a and b = z.b

    for xml path('')),1,1,'') as level2

    from

    (select a,b from example with (nolock) group by a,b)z

    Finally getting back to this post...

    The only result I get back from your code using the test data you provided is an error.

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the varchar value ', ' to data type int.

    --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

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

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