I have a need to query some data and string all my results by id. I am fairly close to the results but stuck on the final piece. Any help would be greatly appreciated.
Here's the scenario: My data looks as follows:
I want to query it and end up with the results all strung together under each UserID as follows:
I know if I use the following code I can string all the results together
select *, Cast((SELECT Results + ',' FROM #temp1 FOR XML PATH('')) as varchar(max) ) as Results from #temp1
But I can't figure out how to break it down by individual UserID. Any help is greatly appreciated. Thanks in advance
If you are using SQL 2005, you could do something like this:
-- Create Comma Delimited list from multiple rows
-- From Tony Roberson
-- SQL 2005
DECLARE @.MailingList table
( IndividualName nvarchar(100) not null,
ListName nvarchar(10) not null
)
INSERT INTO @.MailingList VALUES( 'Bill Smith', 'List A' )
INSERT INTO @.MailingList VALUES( 'Bill Smith', 'List B' )
INSERT INTO @.MailingList VALUES( 'Bill Smith', 'List C' )
INSERT INTO @.MailingList VALUES( 'Sally Jones', 'List A' )
INSERT INTO @.MailingList VALUES( 'Sally Jones', 'List B' )
INSERT INTO @.MailingList VALUES( 'Hector Lopez', 'List A' )
SELECT DISTINCT
IndividualName,
List = substring( ( SELECT ', ' + ListName as [text()]
FROM @.MailingList m2
WHERE m2.IndividualName = m1.IndividualName
FOR XML path(''), elements
), 3, 1000
)
FROM @.MailingList m1
/*
Gives this result:
Alex R List A
Joe R List A, List B
Tony R List A, List B, List C
*/
|||Outstanding thanks!!!
Final question, in the query
...FOR XML path(''), elements
), 3, 1000
)
what does the 3, 1000 do
|||They are the second and third arguments for the substring function. They remove the first two characters from the final string (up to 1000 characters starting at the third).
The list is generated with ', ' in front of each member (i.e. ', a, b, c, d, e') whereas you do not want a comma/space in front of the first item so this removes the first ', ' from the front of the list.
|||Once again thanks I really appreciate the help|||As Dhericean noted, those are parameters for the substring() function.
You could eliminate the space in [ SELECT ', ' ] and change the value 3 to 2. And you can increase or reduce the value 1000 to more closely represent the lenght of the final concatenated string.
|||Arnie -
A while back you provided me this solution to a need I had. Many thanks, I have use it often and with many tweeks. Now I am trying to do something similar and I am stuck, but I can't figure out why....
Here is our process and what is happening:
This is our preliminary query... it gets the group of raw data we will be analyzing (and it works)
SELECT SITEDATA.SITEID
, SITEDATA.ANOMALYT1 + ','
+ SITEDATA.ANOMALYT2 + ','
+ SITEDATA.ANOMALYT3 + ','
+ SITEDATA.ANOMALYT4 + ','
+ SITEDATA.ANOMALYT5 + ','
+ SITEDATA.ANOMALYT6 + ','
+ SITEDATA.ANOMALYT7 + ','
+ SITEDATA.ANOMALYT8 AS ANOMALIES
, SITEDATA.CALC_DATE, SITE.PROPID, SITE.LOCATION
INTO [#TEMP1]
FROM SITEDATA INNER JOIN SITE ON SITEDATA.SITEID = SITE.SITEID
WHERE (SITEDATA.SITEID = 907) AND (SITEDATA.CALC_DATE BETWEEN '1/1/06' AND '12/31/06')
Here is a sample of results from the above Query (Note, there are values in the Anomalies column):
SITEID Anomalies Calc_Date PropID Location
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
907 ,2,3,,,6,,, 2006-03-26 00:00:00.000 40 5
Now we want to string all the data in the Anomalies column together by Distinct Calc_Date. To do this we execute the following Proc:
SELECT DISTINCT CALC_DATE, SITEID, PROPID, LOCATION, ANOMALIES = SUBSTRING( (
SELECT ',' + ANOMALIES AS [text()] FROM #TEMP1 T1 WHERE T1.SITEID = T2.SITEID FOR XML PATH(''), ELEMENTS ), 3, 1000 )
INTO #TEMP2
FROM #TEMP1 T2
This procedure strings the Anomalies together, but the data appears to be lost, i.e. we should get ,2,3,,,6,,,2,3,6,,,,2,3,,,6, etc. but instead all we get is ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
I thought the issue might be caused by date and time not being distinct, so I tried converting the date to varchar and rerunning, but no difference.... Any thoughts?|||
Jim,
Your query worked perfectly for me. (minor formatting alterations...)
SET NOCOUNT ON
DECLARE @.Temp1 table
( RowID int IDENTITY,
SiteID int,
Anomalies varchar(100),
Calc_Date datetime,
PropID int,
Location int
)
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
INSERT INTO @.Temp1 VALUES ( 907, ',2,3,,,6,,,', '2006-03-26 00:00:00.000', 40, 5 )
SELECT DISTINCT
Calc_Date,
SiteID,
PropID,
LOCATION,
Anomalies = substring( ( SELECT ',' + Anomalies AS [text()]
FROM @.Temp1 T2
WHERE T1.SITEID = T2.SITEID
FOR XML PATH(''), ELEMENTS
), 3, 1000
)
INTO #Temp2
FROM @.Temp1 T1
SELECT *
FROM #Temp2
Calc_Date SiteID PropID LOCATION Anomalies
-- -- --
2006-03-26 00:00:00.000 907 40 5 2,3,,,6,,,,,2,3,,,6,,,,,2,3,,,6,,,,,
(Anomalies repetition removed for display purposes...)
|||Thanks again Arnie.I couldn't replicate your success, however I figured out a different query to and up with the same results. Because the DS is small, I just searched the Anomalies field without combining them, then I looked for distinct date. I think it ended up being faster anyway
