查询我需要这样的
问题描述:
这是我的查询查询我需要这样的
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
,我得到这样
CALENDAR_NAME CALENDAR_ID CALENDARDAY_DAYID
-------------------------------------------------- ----------- -----------------
Test 1 1
Test 1 2
Test 1 3
Test 1 4
Test 1 6
结果,但我需要这样的
calendar_name calendar_dayid calendar_dayid calendar_dayid calendar_dayid calendar_dayid
test 1 2 3 4
答
下面是一个查询使用PIVOT运营商
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
请格式化您的问题。 – vissi 2011-03-12 09:37:40
看看使用'PIVOT'命令 – Tony 2011-03-12 09:45:12
为什么你需要一个结果与5个相同名字的列? – 2011-03-12 13:30:49