October 15, 2007 at 2:35 am
hi all,
I have 2 tables, Incident and Incident_activity. Incident table having Inc_ID as primary key. Now I got resultset as
Inc_ID Inc_activity
1 xxx
1 yyy
1 zzz
I want resultset as
Inc_ID Inc_activity
1 xxx,yyy,zzz
I don't want to use cursors and temptable. Can anybody help me to get the output as above.
October 15, 2007 at 3:25 am
You could use a user defined function to do this. If you are on 2005 then there is a nice trick to use xml path, or you could revert to CLR. Solutions to the first two (so both for 2000 and 2005) are shown on http://qa.sqlservercentral.com/Forums/Topic391111-338-1.aspx
Regards,
Andras
October 15, 2007 at 5:32 am
Not forgetting (modest cough) Enumerating Group Members - which works in 2000 (for short resultsets) -but I agree that the XML trick is very cute
Best wishes,
Phil Factor
October 15, 2007 at 8:35 am
This is something called cross table. An excellent example can be found in
October 15, 2007 at 11:10 am
I just got bored doing something else and suddenly thought of a completely different way of doing this. Using the slightly more complicated data example I used in my BLOG entry 'Enumerating Group Members' - and it will work in SQL Server 2000 as well (the only problem with earlier versions will be the Varchar size restrictions)
[font="Courier New"]CREATE TABLE #schedule
(
EventStart DATETIME,
description VARCHAR(2000)
)
/* so now we can get some sample data into the table */
INSERT INTO #schedule ( EventStart, description )
SELECT '12 Oct 2007 10:00', 'Meeting with Bill'
INSERT INTO #schedule ( EventStart, description )
SELECT '12 Oct 2007 12:00', 'visit Crawley site'
INSERT INTO #schedule ( EventStart, description )
SELECT '12 Oct 2007 13:00', 'Lunch with Evelyn'
INSERT INTO #schedule ( EventStart, description )
SELECT '12 Oct 2007 14:00', 'Review of CDW27'
INSERT INTO #schedule ( EventStart, description )
SELECT '12 Oct 2007 17:00', 'admin'
INSERT INTO #schedule ( EventStart, description )
SELECT '13 Oct 2007 10:00', 'Finance committee'
INSERT INTO #schedule ( EventStart, description )
SELECT '13 Oct 2007 12:00', 'lunch with Bob'
INSERT INTO #schedule (
EventStart, description )
SELECT '13 Oct 2007 14:00', 'Weekly SH meeting'
INSERT INTO #schedule ( EventStart, description )
SELECT '14 Oct 2007 11:00', 'interviews'
INSERT INTO #schedule ( EventStart, description )
SELECT '14 Oct 2007 15:00', 'office plan presentation'
INSERT INTO #schedule ( EventStart, description )
SELECT '14 Oct 2007 16:00', 'performance reviews'
DECLARE @accumulation VARCHAR(7000), @Date CHAR(11)
DECLARE @grouping TABLE (MyID INT IDENTITY(1,1),
date CHAR(11),
description VARCHAR(990),
[next] INT,
accumulation VARCHAR(7000))
INSERT INTO @grouping(date,description)
SELECT CONVERT(CHAR(11),EventStart,113)
,
RIGHT(CONVERT(CHAR(17),EventStart,113),5)+'- '+description
FROM #schedule ORDER BY eventstart
UPDATE @grouping SET
@Accumulation=[accumulation]=
CASE WHEN date<>COALESCE(@date,'')
THEN description
ELSE @Accumulation+', '+description
END,
@Date=date=date
SELECT Date,[events]=MAX(accumulation)
FROM @grouping GROUP BY date[/black][/font]
[font="Courier New"]Date events
----------- ------------------------------------------------------------------------------
12 Oct 2007 10:00- Meeting with Bill, 12:00- visit Crawley site, 13:00- Lunch with Evelyn,
14:00- Review of CDW27, 17:00- admin
13 Oct 2007 10:00- Finance committee, 12:00- lunch with Bob, 14:00- Weekly SH meeting
14 Oct 2007 11:00- interviews, 15:00- office plan presentation, 16:00- performance reviews[/font]
Best wishes,
Phil Factor
October 15, 2007 at 8:39 pm
Phil,
What product are you using to format the code so nicely?
--Jeff Moden
October 15, 2007 at 11:47 pm
I used user defined function to accomplish this task..
Thank you all.:satisfied:
October 16, 2007 at 1:57 am
Jeff,
Obviously, I'd prefer to do cut 'n paste from SSMS/QA, but this seems to be denied us now for some reason. I've got an adaption of the S-T Prettifier doing IFCodes. As soon as the site has all its bugs fixed we'll release it for everyone. It will be either as a stored procedure or as a web tool. It is able to put code inline and in code blocks. The issues we have on the site at the moment are:
1/ if you edit a post or preview it, the spaces disappear and the code loses its indentation,
2/ The code block has a grey background. As grey is one of the highlighting colours used in the Query Analyser, this makes code difficult to read.
3/ indentation in inline code is best done with non-breaking spaces, but these are stripped out by the forum Software. the N-spaces and M-Spaces aren't stripped out but aren't recognised by IE6!
4/ The font sizes 1..7 aren't particularly useful. Font size 2 is a bit big, and font size 1 is a bit too small. I can't quite see why anyone would use 4..7
Best wishes,
Phil Factor
October 16, 2007 at 9:08 am
Thanks Phil... guess I'll just keep doing CPR (Cut, Paste, 'n' Replace) using Word until they fix the forum code... I just copy from QA into Word and replace all spaces with non-breaking spaces (& nbsp without the extra space).
The really disappointing thing about all of this is that the code IFCODE used to work just find for about 24 hours after they switched to this new forum... somebody did something wrong...
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply