Interesting Question in SQL

  • Hi Everyone....

    I have a table (no Primary Key defined) with some value and I need to create a view on that table to get a desired output. Sample Data for both is as below. I would really appreciate if someone can take the pain of helping me out here....Thanks a ton in advance.

    TABLE (Input)

    IDNameCode

    1abcA

    1abcB

    2ghtF

    3jkuG

    4xyzP

    4xyzQ

    5rstD

    DESIRED VIEW RESULT (Output)

    IDNameCode

    1abcA,B

    2ghtF

    3jkuG

    4xyzP,Q

    5rstD

    As it should be clear from above figures and values, if column 'Code' has multiple values for same column 'ID', then they need to be shown as single record with commas. e.g. 'A,B'

  • DECLARE @t TABLE(ID INT, Name CHAR(3),Code CHAR(1))

    INSERT INTO @t(ID,Name,Code)

    VALUES

    (1, 'abc', 'A'),

    (1, 'abc', 'B'),

    (2, 'ght', 'F'),

    (3, 'jku', 'G'),

    (4, 'xyz', 'P'),

    (4, 'xyz', 'Q'),

    (5, 'rst', 'D');

    SELECT a.ID,

    a.Name,

    STUFF((SELECT ',' + b.Code AS "text()"

    FROM @t b

    WHERE b.ID = a.ID

    AND b.Name = a.Name

    ORDER BY b.Code

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS Code

    FROM @t a

    GROUP BY a.ID,a.Name

    ORDER BY a.ID,a.Name;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Terrific....Exactly what I needed and such a prompt response.

    Super Thanks 😀

  • Thanks Mark.....

    Though I was trying to tweak your query for below requirement of mine. Basically there are mutiple columns which can have duplicate values and those need to be concatenated as well.

    Table Data

    IDCol A Col B Col C

    1India VP Mumbai

    2Aus Consultant Sydney

    2Aus VP Melbourne

    3France Consultant Paris

    4Swiss SC Zurich

    4Swiss Consultant Geneva

    4Swiss VP Lusanne

    5Germany Consultant Koln

    5Germany SC Berlin

    Output Data

    IDCol A Col B Col C

    1India VP Mumbai

    2Aus Consultant, VP Sydney, Melbourne

    3France Consultant Paris

    4Swiss SC, Consultant, VP Zurich, Geneva, Lusanne

    5Germany Consultant, SC Koln, Berlin

    Again.....Thanks a lot in advance.

  • You can make some changes to Mark's query as below

    DECLARE @t TABLE( ID INT, ColA CHAR(20), ColB CHAR(20), ColC CHAR(20) )

    INSERT INTO @t(ID, ColA, ColB, ColC )

    VALUES

    ( 1, 'India', 'VP', 'Mumbai' ),

    ( 2, 'Aus', 'Consultant', 'Sydney' ),

    ( 2, 'Aus', 'VP', 'Melbourne' ),

    ( 3, 'France', 'Consultant', 'Paris' ),

    ( 4, 'Swiss', 'SC', 'Zurich' ),

    ( 4, 'Swiss', 'Consultant', 'Geneva' ),

    ( 4, 'Swiss', 'VP', 'Lusanne' ),

    ( 5, 'Germany', 'Consultant', 'Koln' ),

    ( 5, 'Germany', 'SC', 'Berlin' );

    SELECT a.ID,

    a.ColA,

    STUFF((SELECT ',' + b.ColB AS "text()"

    FROM @t b

    WHERE b.ID = a.ID

    AND b.ColA = a.ColA

    ORDER BY b.ColB

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') + ' ' +

    STUFF((SELECT ',' + b.ColC AS "text()"

    FROM @t b

    WHERE b.ID = a.ID

    AND b.ColA = a.ColA

    ORDER BY b.ColC

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS Col

    FROM @t a

    GROUP BY a.ID,a.ColA

    ORDER BY a.ID,a.ColA;


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Hi Mark,

    I am truly amazed by the quick response I am getting on this forum. I must say a Big Thanks before anything else.

    On the code that you have provided, it gives a good hint but not the exact solution I am looking for

    My input Data has 4 columns and Output should also have 4 columns, whereas your query result gives 3 columns only. Its combining column B and column C.

    I am attaching a screenshot (Test Data.jpg) which shows

    1) Sample Input Data in Table

    2) Expected output in the View

    3) Actual result that I am getting using your query.

    Though, I am trying my hands to tweak the query and hope that I would get to the result myself before anyone else, if so, I will post the correct query as well.

    But if anyone else can help me out here that would be just too good.

  • This should give the result you are looking for..

    DECLARE @t TABLE( ID INT, ColA CHAR(20), ColB CHAR(20), ColC CHAR(20) )

    INSERT INTO @t(ID, ColA, ColB, ColC )

    VALUES

    ( 1, 'India', 'VP', 'Mumbai' ),

    ( 2, 'Aus', 'Consultant', 'Sydney' ),

    ( 2, 'Aus', 'VP', 'Melbourne' ),

    ( 3, 'France', 'Consultant', 'Paris' ),

    ( 4, 'Swiss', 'SC', 'Zurich' ),

    ( 4, 'Swiss', 'Consultant', 'Geneva' ),

    ( 4, 'Swiss', 'VP', 'Lusanne' ),

    ( 5, 'Germany', 'Consultant', 'Koln' ),

    ( 5, 'Germany', 'SC', 'Berlin' );

    SELECT a.ID,

    a.ColA,

    STUFF((SELECT ',' + b.ColB AS "text()"

    FROM @t b

    WHERE b.ID = a.ID

    AND b.ColA = a.ColA

    ORDER BY b.ColB

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') as ColB,

    STUFF((SELECT ',' + b.ColC AS "text()"

    FROM @t b

    WHERE b.ID = a.ID

    AND b.ColA = a.ColA

    ORDER BY b.ColC

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColC

    FROM @t a

    GROUP BY a.ID,a.ColA

    ORDER BY a.ID,a.ColA;

    In case you are wondering what the code does, have a look at the article mentioned in the link below

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

    Edit: Added link to an article


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • This is Crazy and I am overjoyed.

    Thanks to everone who took the pain to look into my Issue and been kind enough to give their precious time. Thanks Again..... 🙂

  • Hi,

    I had to come back again......

    I used below code, only change in what has been suggested earlier is addition of an additional record so that I get duplicate values for 5, Germany, Berlin.

    DECLARE @t TABLE( ID INT, ColA nvarCHAR(max), ColB nvarCHAR(max), ColC nVarCHAR(max) )

    INSERT INTO @t(ID, ColA, ColB, ColC )

    VALUES

    ( 1, 'India', 'VP', 'Mumbai' ),

    ( 2, 'Aus', 'Consultant', 'Sydney' ),

    ( 2, 'Aus', 'VP', 'Melbourne' ),

    ( 3, 'France', 'Consultant', 'Paris' ),

    ( 4, 'Swiss', 'SC', 'Zurich' ),

    ( 4, 'Swiss', 'Consultant', 'Geneva' ),

    ( 4, 'Swiss', 'VP', 'Lusanne' ),

    ( 5, 'Germany', 'Consultant', 'Koln' ),

    ( 5, 'Germany', 'SC', 'Berlin' ),

    ( 5, 'Germany', 'VP', 'Berlin' );

    SELECT a.ID,

    a.ColA,

    STUFF((SELECT ',' + b.ColB AS "text()"

    FROM @t b

    WHERE b.ID = a.ID

    AND b.ColA = a.ColA

    ORDER BY b.ColB

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColB,

    STUFF((SELECT ',' + b.ColC AS "text()"

    FROM @t b

    WHERE b.ID = a.ID

    AND b.ColA = a.ColA

    ORDER BY b.ColC

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColC

    FROM @t a

    GROUP BY a.ID,a.ColA

    ORDER BY a.ID,a.ColA;

    In output, I want, for last record for column C, to show Berlin Once and twice.

    Any suggestions around that one ? As I would have many multiple values in my actual tables and imagine Berlin repeating 5-6 times would look weird.

    Last record for column C should be

    "Berlin, Koln" and not

    "Berlin, Berlin, Koln"

    Thanks in advance.

  • To get unique vaues you just need to group the entries for the columns (that applies to all kind of queries, not only to "FOR XML" ones :hehe: ):

    SELECT a.ID,

    a.ColA,

    STUFF((SELECT ',' + b.ColB AS "text()"

    FROM @t b

    WHERE b.ID = a.ID

    AND b.ColA = a.ColA

    GROUP BY b.ColB -- << here

    ORDER BY b.ColB

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColB,

    STUFF((SELECT ',' + b.ColC AS "text()"

    FROM @t b

    WHERE b.ID = a.ID

    AND b.ColA = a.ColA

    GROUP BY b.ColC -- << and here

    ORDER BY b.ColC

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColC

    FROM @t a

    GROUP BY a.ID,a.ColA

    ORDER BY a.ID,a.ColA;

    _____________
    Code for TallyGenerator

Viewing 10 posts - 1 through 9 (of 9 total)

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