Q:

Query i need like this

This is my query 
SELECT CAL.CALENDAR_NAME,CAL.CALENDAR_ID,CALDAY.CALENDARDAY_DAYID 
FROM lms_calendar AS CAL
  LEFT JOIN LMS_CALENDARDAY AS CALDAY 
  ON CAL.CALENDAR_ID = CALDAY.CALENDARDAY_CALENDARID

and I get results like this
CALENDAR_NAME                                      CALENDAR_ID CALENDARDAY_DAYID
-------------------------------------------------- ----------- -----------------
Test                                               1           1
Test                                               1           2
Test                                               1           3
Test                                               1           4
Test                                               1           6

But I need like this
calendar_name  calendar_dayid calendar_dayid calendar_dayid calendar_dayid calendar_dayid  
test              1                 2              3              4            

A:

Here is a query that uses the PIVOT operator
SELECT calendar_name, 
    [1] AS calendar_dayid, 
    [2] AS calendar_dayid, 
    [3] AS calendar_dayid, 
    [4] AS calendar_dayid, 
    [5] AS calendar_dayid
FROM (
    SELECT CAL.CALENDAR_NAME,CAL.CALENDAR_ID,CALDAY.CALENDARDAY_DAYID 
    FROM lms_calendar AS CAL
      LEFT JOIN LMS_CALENDARDAY AS CALDAY 
      ON CAL.CALENDAR_ID = CALDAY.CALENDARDAY_CALENDARID
    ) AS src
PIVOT (
    MAX(calendarday_dayid)
    FOR calendarday_dayid IN ([1], [2], [3], [4], [5])
    ) AS pvt

