March 27, 2007 at 3:56 am
Hi,
i hav a query
"select customer_id, programcode
from salesop (nolock)
where customer_id = 290780" which returns the resultset as
customer_id programcode ----------- ----------- 290780 AY 290780 LT 290780 ILS 290780 AY
i need to group all the programcode values n to get the result as
customer_id programcode ----------- ----------- 290780 AY,LT,ILS,AY
i wanted to group without a where clause so it return thousands of rows...
can u help me out...??
Thanks in advance...
March 27, 2007 at 5:04 am
to accomplish that you may need additional processing. now create a cursor based on that same query, sort by customer_id and accumulate in a variable all the different program_codes, once the customer_id changes insert the customer_id and the accum_prog_codes (as a string) into a temp table. at the the end of the cursor select from the temp table, as your final record set if creating a SP. I'm sure some will suggest to use the new WITH recursuve structure, which I'm not familiar with yet. wait for that suggestion if not comfortable with the cursor idea.
March 27, 2007 at 5:05 am
-- Prepare sample data
DECLARE
@Sample TABLE (ID INT, Code VARCHAR(3))
INSERT
@Sample
SELECT
290780, 'AY' UNION ALL
SELECT
290780, 'LT' UNION ALL
SELECT
290781, 'ILS' UNION ALL
SELECT
290780, 'AY'
-- Show the expected output
SELECT
DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM
@Sample AS s1
ORDER
BY s1.ID
N 56°04'39.16"
E 12°55'05.25"
March 27, 2007 at 5:36 am
Thank u so much larsson...
it really worked out well...
March 27, 2007 at 5:39 am
Also try this if you don't want duplicates in the CODES
SELECT
DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply