SQL query to get Rows values to a column

Question

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

SQL Table Data

CustomerId     Name             Type        FoodName
=============   =============   =========   ==========
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          BREAKFAST   Bread
C0002           JOSEPH          BREAKFAST   Jam
C0002           JOSEPH          BREAKFAST   Tea

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

CustomerId      Name            BREAKFAST           LUNCH   
============    =============   ================    ========== 
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.

I Tried the following query

with DietList(CustomerId,Name,BREAKFAST, DINNER) as
(
select distinct CustomerId 'CustomerId', Name as 'Name'
,(
    select a.foodName + ',' as [text()] from

    (select AdmnNo,foodName from DietCard) as a    

    where c.CustomerId = a.CustomerId and c.DietSchedule = 'BREAKFAST' and  convert(varchar, [c.ScheduleDate], 112) = '20180402' 
    for xml path('')    
    ) 'EVENINGTEA'
from DietCard 
)
,(
    select a.foodName + ',' as [text()] from

    (select AdmnNo,foodName from DietCard) as a    

    where c.CustomerId = a.CustomerId and c.DietSchedule = 'DINNER' and  convert(varchar, [c.ScheduleDate], 112) = '20180402' 
    for xml path('')    
    ) 'DINNER'
from DietCard 
)
select * from DietListList where BREAKFAST is not null or DINNER is not null

Thanks in Advance.

I tries https://forums.asp.net/t/1740188.aspx?Row+Values+in+a+Column but not getting my result. — user1460901, Apr 02 '18 at 12:22
But what was the SQL you actually wrote? Can you put that into your question please? — Thom A, Apr 02 '18 at 12:23
Might want to also add which version of SQL Server you're on so that we have all the information needed to help out. — MK_, Apr 02 '18 at 12:24
This question is duplicate of : https://stackoverflow.com/questions/16798930/pivot-and-comma-separated-value — Prabhat G, Apr 02 '18 at 12:55
Answer added here: `https://stackoverflow.com/a/49611871/5522140` — Manprit Singh Sahota, Apr 02 '18 at 13:06
@user1460901 I have added my answer using cte at: https://stackoverflow.com/a/49613343/9236062 — Aura, Apr 02 '18 at 14:57
@user1460901 Hi find the sultion for your requirement by following this link http://sqlfiddle.com/#!18/422b7/2 — Sreenu131, Apr 02 '18 at 15:34
You should ask a new question if you are having trouble with the code in the duplicate question. Adding another unrelated topic here is not likely to get this question reopened. — NightOwl888, Apr 03 '18 at 02:05

SQL query to get Rows values to a column

0 Answers0