locked
Rows in Column SQL Query RRS feed

  • Question

  • User1724124194 posted

    Hi,

    I am looking for a SQL query to get sql data to row data to a colum. 

    SQL Table Data

    CustomerCode CustomerName Type Items
    C0001 Thomas BREAKFAST Milk
    C0001 Thomas BREAKFAST Bread
    C0001 Thomas BREAKFAST Egg
    C0001 Thomas LUNCH Rice
    C0001 Thomas LUNCH Fish Curry
    C0001 Thomas LUNCH Lessy
    C0002 JOSEPH DINNER Bread
    C0002 JOSEPH DINNER Jam
    C0002 JOSEPH BREAKFAST Tea

    The above is the SQL Table Data and I need this data in following format. Can anybody help me two create a SQL Qyery to get below output ?

    CustomerCode CustomerName BREAKFAST LUNCH DINNER
    C0001 Thomas Milk, Bread, Egg Rice, Fish Curry, Lessy
    C0002 JOSEPH Tea Bread, Jam

    Please help me to create SQL Query to get the above output.

    Thanks in Advance.

    Monday, April 2, 2018 12:09 PM

Answers

  • User-369506445 posted

    hi

    please try below query :

    SELECT CustomerCode,CustomerName,
      (select   STUFF((SELECT ',' + US.Items 
              FROM MyTable US   
              where [type]='BREAKFAST'and CustomerCode=ss.CustomerCode
              FOR XML PATH('')), 1, 1, '') ) as BREAKFAST,
       (select   STUFF((SELECT ',' + US.Items 
              FROM MyTable US   
              where [type]='LUNCH'  and CustomerCode=ss.CustomerCode     
              FOR XML PATH('')), 1, 1, '') ) as LUNCH,
        (select   STUFF((SELECT ',' + US.Items 
              FROM MyTable US   
              where [type]='DINNER'  and CustomerCode=ss.CustomerCode     
              FOR XML PATH('')), 1, 1, '') ) as DINNER
      FROM [TestDB].[dbo].[MyTable] ss  
      
      group by CustomerCode,CustomerName

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 2, 2018 12:47 PM