May 17, 2006 at 3:39 am
SELECT dbo.MMMRDESC.DOCUMENT_NO,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '5' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_5,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '10' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_10,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '11' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_11,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '12' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_12,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '13' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_13,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '14' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_14,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '15' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_15,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '16' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_16,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '17' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_17,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '18' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_18,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '18' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_19,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '20' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_20,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '21' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_21,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '22' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_22,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '23' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_23,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '24' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_24,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '25' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_25,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '26' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_26,
MAX(CASE WHEN dbo.MMMRDESC.LINE_NO = '27' THEN dbo.MMMRDESC.LINE_VALUE ELSE '' END) AS LINE_27
FROM
dbo.MMMRDESC
WHERE dbo.MMMRDESC.DOCUMENT_NO IN
('208101001', '208101002', '208101101', '208101102', '208101103',
'208111001', '208111002', '208111003','208111004', '208111005',
'208111006', '208111007', '208112050', '208112120', '208113001',
'208113002', '208113003', '208113004', '208113005',
'208113007', '208113008', '208113009', '208121001',
'208121002', '208121003', '208121004', '208121005',
'208121006', '208121008', '208121009',
'208121010', '208121011', '208121012', '208121015')
GROUP BY dbo.MMMRDESC.DOCUMENT_NO
ORDER BY dbo.MMMRDESC.DOCUMENT_NO
May 17, 2006 at 8:12 am
Assuming that the intent of this query is to create a cross-tab, you pretty much have it.
Although, if you're using SQL Server 2005, you can look into the PIVOT keyword.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply