January 25, 2011 at 1:33 am
I have a One table like this :
CREATE TABLE STATUS_MASTERS (FORMAT_NAME VARCHAR(150)NULL,FORMAT_ID VARCHAR(10) )
GO
INSERT INTO STATUS_MASTERS(FORMAT_NAME,FORMAT_ID )
SELECT 'A+B In-Progress','' UNION ALL
SELECT 'A+B In-Progress','PDF' UNION ALL
SELECT 'A+B In-Progress','WORD' UNION ALL
SELECT 'B+C In-Progress',''
GO
I want the results summary from the above table like
in single column
The output:
A+B In-Progress-(pdf)(word),B+C In-Progress
January 25, 2011 at 2:08 am
How about this ?
SELECT REPLACE ( (SM_Outer.FORMAT_NAME + ' - ' +
STUFF ( ( SELECT ' ('+FORMAT_ID + ') '
FROM STATUS_MASTERS SM_Inner
WHERE SM_Inner.FORMAT_NAME = SM_Outer.FORMAT_NAME
ORDER BY FORMAT_NAME
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0))
),' ()', '')AS Result_Values
FROM STATUS_MASTERS SM_Outer
GROUP BY SM_Outer.FORMAT_NAME ;
January 25, 2011 at 2:12 am
How fast, Mr. Coffe!
This is an alternative syntax:
;WITH Formats AS (
SELECT FORMAT_NAME
FROM STATUS_MASTERS
GROUP BY FORMAT_NAME
)
SELECT FORMAT_NAME +
ISNULL(' - ' + (
SELECT DISTINCT '(' + FORMAT_ID + ')' AS [text()]
FROM STATUS_MASTERS
WHERE FORMAT_NAME = F.FORMAT_NAME
AND FORMAT_ID <> ''
ORDER BY 1
FOR XML PATH('')
),'')
FROM Formats AS F
-- Gianluca Sartori
January 25, 2011 at 2:19 am
Gianluca Sartori (1/25/2011)
How fast, Mr. Coffe!
I may be fast, but u are so precise Sir!! mine will produce an extra hyphen for empty strings, your's wont!! Beautiful!!
BTW, Happy New Year,Gian! 🙂
January 25, 2011 at 2:32 am
Many Thanks for all support.......
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply