October 11, 2010 at 9:20 am
I use the following to build a csv column from table "A" (one) and table "B" (to many). It works fine but is very slow exspecially when building multiple csv columns.
Would anyone please offer an alternative solution - that is faster, more efficient.
I'm trying to devise a soluton that uses a numbers table to build the string but wondering if there is a more efficient solution. Any help is greatly appreciated.
Thanks.
SELECT
strReceiptno AS [Receipt]
, dteCompleted AS [DATE]
, STUFF((SELECT DISTINCT ', ' + CAST(cla.strName AS VARCHAR(1000))
FROM dbo.tblControlLog c2
JOIN dbo.tblControlLogAssociates AS cla
ON c2.guidControlLogID=cla.guidControlLogID
WHERE c.guidControlLogID = c2.guidControlLogID
FOR XML PATH('')),1, 1, '') AS [Associates]
FROM dbo.tblControlLog AS c
The following is the result of the query above:
DECLARE @base TABLE (Receipt NVARCHAR(50), XDate NVARCHAR(20), Associates NVARCHAR(1000))
INSERT INTO @base Values('SGE101007090359','10/07/2010', 'James Birming, Kenny Turrentine, Thaddeus Edwards')
INSERT INTO @base Values('SGE101007081241','10/07/2010', 'Matt Eddy')
INSERT INTO @base Values('SGE101007105436','10/07/2010', 'Michael Hirschbine')
INSERT INTO @base Values('SGE101007083149','10/07/2010', 'Marion Franks, Wally Bohlen Jr')
INSERT INTO @base Values('SGE101007093245','10/07/2010', 'Kenny Turrentine')
INSERT INTO @base Values('SGE101007134725','10/07/2010', 'Thaddeus Edwards')
INSERT INTO @base Values('SGE101007081316','10/07/2010', 'Chris Swanson, Eric Bouter')
INSERT INTO @base Values('SGE101007121918','10/07/2010', 'James Birming, Thaddeus Edwards')
INSERT INTO @base Values('SGE101007142855','10/07/2010', 'James Birming')
INSERT INTO @base Values('SGE101007063342','10/07/2010', 'Matt Eddy, Scott Gearhart')
INSERT INTO @base Values('SGE101007093649','10/07/2010', 'Chris Swift Jr., Rob Wilcox')
INSERT INTO @base Values('SGE101007081119','10/07/2010', 'James Birming')
INSERT INTO @base Values('SGE101007081032','10/07/2010', 'Chris Vogel, Matt Zulawski, Rob Wilcox')
INSERT INTO @base Values('SGE101007141045','10/07/2010', 'Kenny Turrentine, Matt Zulaw, Michael Hirschbine')
INSERT INTO @base Values('SGE101007134524','10/07/2010', 'Scott Gearhart')
INSERT INTO @base Values('SGE101007102143','10/07/2010', 'Rob Wilcox')
INSERT INTO @base Values('SGE101007143553','10/07/2010', 'Thaddeus Edwards')
INSERT INTO @base Values('SGE101007082348','10/07/2010', 'Matt Eddy')
INSERT INTO @base Values('SGE101007112118','10/07/2010', 'Demetrice Tisdale')
INSERT INTO @base Values('SGE101007134419','10/07/2010', 'Matt Zulaw')
INSERT INTO @base Values('SGE101007120347','10/07/2010', 'Chris Vogel, Demetrice Tisdale, George Samson')
INSERT INTO @base Values('SGE101007124251','10/07/2010', 'Matt Eddy')
INSERT INTO @base Values('SGE101007143208','10/07/2010', 'Chris Swift Jr., Chris Vogel, George Samson, Matt Eddy')
INSERT INTO @base Values('SGE101007111202','10/07/2010', 'Kenny Turrentine')
INSERT INTO @base Values('SGE101007110638','10/07/2010', 'Michael Hirschbine')
INSERT INTO @base Values('SGE101007131000','10/07/2010', 'Chris Vogel')
INSERT INTO @base Values('SGE101007103749','10/07/2010', 'jerome Barber')
INSERT INTO @base Values('SGE101007090535','10/07/2010', 'Scott Gearhart')
INSERT INTO @base Values('SGE101007102305','10/07/2010', 'Chris Swift Jr.')
INSERT INTO @base Values('SGE101007102107','10/07/2010', 'Wally Bohlen Jr')
INSERT INTO @base Values('SGE101007123229','10/07/2010', 'Rob Wilcox, Roland Ajie')
INSERT INTO @base Values('SGE101007092628','10/07/2010', 'Chris Swanson, George Samson, Michael Hirschbine')
INSERT INTO @base Values('SGE101007133410','10/07/2010', 'Matt Zulaw')
INSERT INTO @base Values('SGE101007121611','10/07/2010', 'Matt Zulaw')
INSERT INTO @base Values('SGE101007124801','10/07/2010', 'Chris Swift Jr.')
INSERT INTO @base Values('SGE101007135219','10/07/2010', 'Thaddeus Edwards')
INSERT INTO @base Values('SGE101007122842','10/07/2010', 'Kenny Turrentine, Michael Hirschbine')
INSERT INTO @base Values('SGE101007091710','10/07/2010', 'Demetrice Tisdale')
INSERT INTO @base Values('SGE101007134623','10/07/2010', 'Scott Gearhart')
INSERT INTO @base Values('SGE101007115233','10/07/2010', 'James Birming, Matt Eddy, Thaddeus Edwards')
INSERT INTO @base Values('SGE101007141656','10/07/2010', 'George Samson, Rob Wilcox')
INSERT INTO @base Values('SGE101007102221','10/07/2010', 'Chris Vogel, Matt Zulaw, Roland Ajie')
INSERT INTO @base Values('SGE101007112836','10/07/2010', 'Wally Bohlen Jr')
INSERT INTO @base Values('SGE101007081433','10/07/2010', 'George Samson, Michael Hirschbine, Roland Ajie')
INSERT INTO @base Values('SGE101007081205','10/07/2010', 'Chris Swift Jr., Demetrice Tisdale, jerome Barber')
INSERT INTO @base Values('SGE101007142441','10/07/2010', 'Matt Zulaw')
--SELECT * FROM @base
The following is the DDL for a straight query of the two tables:
DECLARE @baseT TABLE (Receipt NVARCHAR(50), XDate NVARCHAR(20), Associates NVARCHAR(1000))
INSERT INTO @baseT Values('SGE101007063342','10/07/2010', 'Matt Eddy')
INSERT INTO @baseT Values('SGE101007063342','10/07/2010', 'Scott Gearhart')
INSERT INTO @baseT Values('SGE101007081032','10/07/2010', 'Chris Vogel')
INSERT INTO @baseT Values('SGE101007081032','10/07/2010', 'Matt Zulaw')
INSERT INTO @baseT Values('SGE101007081032','10/07/2010', 'Rob Wilcox')
INSERT INTO @baseT Values('SGE101007081119','10/07/2010', 'James Birming')
INSERT INTO @baseT Values('SGE101007081205','10/07/2010', 'Chris Swift Jr.')
INSERT INTO @baseT Values('SGE101007081205','10/07/2010', 'Demetrice Tisdale')
INSERT INTO @baseT Values('SGE101007081205','10/07/2010', 'jerome Barber')
INSERT INTO @baseT Values('SGE101007081241','10/07/2010', 'Matt Eddy')
INSERT INTO @baseT Values('SGE101007081316','10/07/2010', 'Eric Bouter')
INSERT INTO @baseT Values('SGE101007081316','10/07/2010', 'Chris Swanson')
INSERT INTO @baseT Values('SGE101007081433','10/07/2010', 'George Samson')
INSERT INTO @baseT Values('SGE101007081433','10/07/2010', 'Roland Ajie')
INSERT INTO @baseT Values('SGE101007081433','10/07/2010', 'Michael Hirschbine')
INSERT INTO @baseT Values('SGE101007082348','10/07/2010', 'Matt Eddy')
INSERT INTO @baseT Values('SGE101007083149','10/07/2010', 'Wally Bohlen Jr')
INSERT INTO @baseT Values('SGE101007083149','10/07/2010', 'Marion Franks')
INSERT INTO @baseT Values('SGE101007090359','10/07/2010', 'Thaddeus Edwards')
INSERT INTO @baseT Values('SGE101007090359','10/07/2010', 'Kenny Turrentine')
INSERT INTO @baseT Values('SGE101007090359','10/07/2010', 'James Birming')
INSERT INTO @baseT Values('SGE101007090535','10/07/2010', 'Scott Gearhart')
INSERT INTO @baseT Values('SGE101007091710','10/07/2010', 'Demetrice Tisdalev')
INSERT INTO @baseT Values('SGE101007092628','10/07/2010', 'Michael Hirschbine')
INSERT INTO @baseT Values('SGE101007092628','10/07/2010', 'George Samson')
INSERT INTO @baseT Values('SGE101007092628','10/07/2010', 'Chris Swanson')
INSERT INTO @baseT Values('SGE101007093245','10/07/2010', 'Kenny Turrentine')
INSERT INTO @baseT Values('SGE101007093649','10/07/2010', 'Chris Swift Jr.')
INSERT INTO @baseT Values('SGE101007093649','10/07/2010', 'Rob Wilcox')
INSERT INTO @baseT Values('SGE101007102107','10/07/2010', 'Wally Bohlen Jr')
INSERT INTO @baseT Values('SGE101007102143','10/07/2010', 'Rob Wilcox')
INSERT INTO @baseT Values('SGE101007102221','10/07/2010', 'Roland Ajie')
INSERT INTO @baseT Values('SGE101007102221','10/07/2010', 'Chris Vogel')
INSERT INTO @baseT Values('SGE101007102221','10/07/2010', 'Matt Zulaw')
INSERT INTO @baseT Values('SGE101007102305','10/07/2010', 'Chris Swift Jr.')
INSERT INTO @baseT Values('SGE101007103749','10/07/2010', 'jerome Barber')
INSERT INTO @baseT Values('SGE101007105436','10/07/2010', 'Michael Hirschbine')
INSERT INTO @baseT Values('SGE101007110638','10/07/2010', 'Michael Hirschbine')
INSERT INTO @baseT Values('SGE101007111202','10/07/2010', 'Kenny Turrentine')
INSERT INTO @baseT Values('SGE101007112118','10/07/2010', 'Demetrice Tisdale')
INSERT INTO @baseT Values('SGE101007112836','10/07/2010', 'Wally Bohlen Jr')
INSERT INTO @baseT Values('SGE101007115233','10/07/2010', 'James Birming')
INSERT INTO @baseT Values('SGE101007115233','10/07/2010', 'Thaddeus Edwards')
INSERT INTO @baseT Values('SGE101007115233','10/07/2010', 'Matt Eddy')
INSERT INTO @baseT Values('SGE101007120347','10/07/2010', 'Demetrice Tisdale')
INSERT INTO @baseT Values('SGE101007120347','10/07/2010', 'eorge Samson')
INSERT INTO @baseT Values('SGE101007120347','10/07/2010', 'Chris Vogel')
INSERT INTO @baseT Values('SGE101007121611','10/07/2010', 'Matt Zulawski')
INSERT INTO @baseT Values('SGE101007121918','10/07/2010', 'Thaddeus Edwards')
INSERT INTO @baseT Values('SGE101007121918','10/07/2010', 'James Birming')
INSERT INTO @baseT Values('SGE101007122842','10/07/2010', 'Kenny Turrentine')
INSERT INTO @baseT Values('SGE101007122842','10/07/2010', 'Michael Hirschbine')
INSERT INTO @baseT Values('SGE101007123229','10/07/2010', 'Roland Ajie')
INSERT INTO @baseT Values('SGE101007123229','10/07/2010', 'Rob Wilcox')
INSERT INTO @baseT Values('SGE101007124251','10/07/2010', 'Matt Eddy')
INSERT INTO @baseT Values('SGE101007124801','10/07/2010', 'Chris Swift Jr.')
INSERT INTO @baseT Values('SGE101007131000','10/07/2010', 'Chris Vogel')
INSERT INTO @baseT Values('SGE101007133410','10/07/2010', 'Matt Zulaw')
INSERT INTO @baseT Values('SGE101007134419','10/07/2010', 'Matt Zulaw')
INSERT INTO @baseT Values('SGE101007134524','10/07/2010', 'Scott Gearhart')
INSERT INTO @baseT Values('SGE101007134623','10/07/2010', 'Scott Gearhart')
INSERT INTO @baseT Values('SGE101007134725','10/07/2010', 'Thaddeus Edwards')
INSERT INTO @baseT Values('SGE101007135219','10/07/2010', 'Thaddeus Edwards')
INSERT INTO @baseT Values('SGE101007141045','10/07/2010', 'Kenny Turrentine')
INSERT INTO @baseT Values('SGE101007141045','10/07/2010', 'Matt Zulaw')
INSERT INTO @baseT Values('SGE101007141045','10/07/2010', 'Michael Hirschbine')
INSERT INTO @baseT Values('SGE101007141656','10/07/2010', 'George Samson')
INSERT INTO @baseT Values('SGE101007141656','10/07/2010', 'Rob Wilcox')
INSERT INTO @baseT Values('SGE101007142441','10/07/2010', 'Matt Zulaw')
INSERT INTO @baseT Values('SGE101007142855','10/07/2010', 'James Birming')
INSERT INTO @baseT Values('SGE101007143208','10/07/2010', 'Matt Eddy')
INSERT INTO @baseT Values('SGE101007143208','10/07/2010', 'George Samson')
INSERT INTO @baseT Values('SGE101007143208','10/07/2010', 'Chris Vogel')
INSERT INTO @baseT Values('SGE101007143208','10/07/2010', 'Chris Swift Jr.')
INSERT INTO @baseT Values('SGE101007143553','10/07/2010', 'Thaddeus Edwards')
--SELECT * FROM @baseT
Straight Query of the two tables:
SELECT
strReceiptno AS Receipt
, dteCompleted AS DATE
, cla.strName
FROM dbo.tblControlLog AS c
INNER JOIN dbo.tblControlLogAssociates AS cla
ON c.guidControlLogID=cla.guidControlLogID
October 11, 2010 at 11:10 am
I don't see actual table definitions in here, so I can't really build the query for you.
What it looks like is one table (ControlLog) is in a one-to-many relationship with the other table (ControlLogAssociates). If that's the case, you should be able to simplify this by using an inline sub-query to the child table, in a query of the main table, and get what you need that way. Should be faster in most circumstances.
Would look something like:
SELECT
strReceiptno AS [Receipt],
dteCompleted AS [DATE],
STUFF((SELECT
', ' + CAST(cla.strName AS VARCHAR(1000))
FROM
dbo.tblControlLogAssociates AS cla
WHERE
c.guidControlLogID = cla.guidControlLogID
FOR
XML PATH('')), 1, 1, '') AS [Associates]
FROM
dbo.tblControlLog AS c;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2010 at 11:57 am
Thanks for the response, but this is exactly (nearly) what I already have. I'm looking for an alternative to this. My query runs approx 75% faster when I remove the subquery that contains the FOR XML construct.
The last table variable is the table definition.
Any help is greatly appreciated.
Thanks,.
October 11, 2010 at 12:53 pm
SSSolice (10/11/2010)
I use the following to build a csv column from table "A" (one) and table "B" (to many). It works fine but is very slow exspecially when building multiple csv columns.Would anyone please offer an alternative solution - that is faster, more efficient.
I'm trying to devise a soluton that uses a numbers table to build the string but wondering if there is a more efficient solution. Any help is greatly appreciated.
Thanks.
SELECT
strReceiptno AS [Receipt]
, dteCompleted AS [DATE]
, STUFF((SELECT DISTINCT ', ' + CAST(cla.strName AS VARCHAR(1000))
FROM dbo.tblControlLog c2
JOIN dbo.tblControlLogAssociates AS cla
ON c2.guidControlLogID=cla.guidControlLogID
WHERE c.guidControlLogID = c2.guidControlLogID
FOR XML PATH('')),1, 1, '') AS [Associates]
FROM dbo.tblControlLog AS c
First, posting DDL and data for results doesn't really help us help you. We can't tell you the best way to get from point A to point B if you only give us point B (the results).
Second, the "dbo.tblControlLog c2" in the subquery is superfluous and probably causing a lot of excess IO. The only purpose this reference is serving is to link c to cla, but, since equality is transitive, you don't need it. If c.guidControlLogID = c2.guidControlLogID and c2.guidControlLogID = cla.guidControlLogID, then c.guidControlLogID = cla.guidControlLogID, so you can eliminate c2.
Using a similar situation on my own database, I took a query similar to your original and rewrote it. For the child table, the scan count and logical reads did not change significantly, but for the parent table the number of logical reads dropped from 250 to 8. (The scan count did not change, but that may simply be the result of running this on a relatively small table.)
Try rewriting your query without the second call to the main table and see how your query performs.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 11, 2010 at 1:34 pm
Thanks for your reply Drew. I did remove the redundant call to tblControlLog but the performance against the presentation data is still too sluggish. Any help with a new perspective is appreciated.
I've split out the two tables for a proper simulation.
Thanks.
tblControlLog
DECLARE @tblControlLog TABLE (Receipt NVARCHAR(50), XDate NVARCHAR(20))
INSERT INTO @tblControlLog Values('SGE101007063342', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007063342', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081032', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081032', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081032', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081119', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081205', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081205', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081205', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081241', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081316', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081316', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081433', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081433', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081433', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007082348', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007083149', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007083149', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007090359', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007090359', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007090359', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007090535', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007091710', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007092628', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007092628', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007092628', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007093245', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007093649', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007093649', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007102107', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007102143', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007102221', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007102221', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007102221', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007102305', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007103749', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007105436', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007110638', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007111202', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007112118', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007112836', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007115233', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007115233', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007115233', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007120347', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007120347', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007120347', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007121611', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007121918', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007121918', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007122842', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007122842', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007123229', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007123229', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007124251', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007124801', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007131000', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007133410', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007134419', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007134524', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007134623', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007134725', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007135219', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007141045', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007141045', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007141045', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007141656', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007141656', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007142441', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007142855', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007143208', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007143208', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007143208', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007143208', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007143553', '10/07/2010')
tblControlLogAssociates
DECLARE @tblControlLogAssociates TABLE (Receipt NVARCHAR(50), Associates NVARCHAR(1000))
INSERT INTO @tblControlLogAssociates Values('SGE101007063342', 'Matt Eddy')
INSERT INTO @tblControlLogAssociates Values('SGE101007063342', 'Scott Gearhart')
INSERT INTO @tblControlLogAssociates Values('SGE101007081032', 'Chris Vogel')
INSERT INTO @tblControlLogAssociates Values('SGE101007081032', 'Matt Zulaw')
INSERT INTO @tblControlLogAssociates Values('SGE101007081032', 'Rob Wilcox')
INSERT INTO @tblControlLogAssociates Values('SGE101007081119', 'James Birming')
INSERT INTO @tblControlLogAssociates Values('SGE101007081205', 'Chris Swift Jr.')
INSERT INTO @tblControlLogAssociates Values('SGE101007081205', 'Demetrice Tisdale')
INSERT INTO @tblControlLogAssociates Values('SGE101007081205', 'jerome Barber')
INSERT INTO @tblControlLogAssociates Values('SGE101007081241', 'Matt Eddy')
INSERT INTO @tblControlLogAssociates Values('SGE101007081316', 'Eric Bouter')
INSERT INTO @tblControlLogAssociates Values('SGE101007081316', 'Chris Swanson')
INSERT INTO @tblControlLogAssociates Values('SGE101007081433', 'George Samson')
INSERT INTO @tblControlLogAssociates Values('SGE101007081433', 'Roland Ajie')
INSERT INTO @tblControlLogAssociates Values('SGE101007081433', 'Michael Hirschbine')
INSERT INTO @tblControlLogAssociates Values('SGE101007082348', 'Matt Eddy')
INSERT INTO @tblControlLogAssociates Values('SGE101007083149', 'Wally Bohlen Jr')
INSERT INTO @tblControlLogAssociates Values('SGE101007083149', 'Marion Franks')
INSERT INTO @tblControlLogAssociates Values('SGE101007090359', 'Thaddeus Edwards')
INSERT INTO @tblControlLogAssociates Values('SGE101007090359', 'Kenny Turrentine')
INSERT INTO @tblControlLogAssociates Values('SGE101007090359', 'James Birming')
INSERT INTO @tblControlLogAssociates Values('SGE101007090535', 'Scott Gearhart')
INSERT INTO @tblControlLogAssociates Values('SGE101007091710', 'Demetrice Tisdalev')
INSERT INTO @tblControlLogAssociates Values('SGE101007092628', 'Michael Hirschbine')
INSERT INTO @tblControlLogAssociates Values('SGE101007092628', 'George Samson')
INSERT INTO @tblControlLogAssociates Values('SGE101007092628', 'Chris Swanson')
INSERT INTO @tblControlLogAssociates Values('SGE101007093245', 'Kenny Turrentine')
INSERT INTO @tblControlLogAssociates Values('SGE101007093649', 'Chris Swift Jr.')
INSERT INTO @tblControlLogAssociates Values('SGE101007093649', 'Rob Wilcox')
INSERT INTO @tblControlLogAssociates Values('SGE101007102107', 'Wally Bohlen Jr')
INSERT INTO @tblControlLogAssociates Values('SGE101007102143', 'Rob Wilcox')
INSERT INTO @tblControlLogAssociates Values('SGE101007102221', 'Roland Ajie')
INSERT INTO @tblControlLogAssociates Values('SGE101007102221', 'Chris Vogel')
INSERT INTO @tblControlLogAssociates Values('SGE101007102221', 'Matt Zulaw')
INSERT INTO @tblControlLogAssociates Values('SGE101007102305', 'Chris Swift Jr.')
INSERT INTO @tblControlLogAssociates Values('SGE101007103749', 'jerome Barber')
INSERT INTO @tblControlLogAssociates Values('SGE101007105436', 'Michael Hirschbine')
INSERT INTO @tblControlLogAssociates Values('SGE101007110638', 'Michael Hirschbine')
INSERT INTO @tblControlLogAssociates Values('SGE101007111202', 'Kenny Turrentine')
INSERT INTO @tblControlLogAssociates Values('SGE101007112118', 'Demetrice Tisdale')
INSERT INTO @tblControlLogAssociates Values('SGE101007112836', 'Wally Bohlen Jr')
INSERT INTO @tblControlLogAssociates Values('SGE101007115233', 'James Birming')
INSERT INTO @tblControlLogAssociates Values('SGE101007115233', 'Thaddeus Edwards')
INSERT INTO @tblControlLogAssociates Values('SGE101007115233', 'Matt Eddy')
INSERT INTO @tblControlLogAssociates Values('SGE101007120347', 'Demetrice Tisdale')
INSERT INTO @tblControlLogAssociates Values('SGE101007120347', 'eorge Samson')
INSERT INTO @tblControlLogAssociates Values('SGE101007120347', 'Chris Vogel')
INSERT INTO @tblControlLogAssociates Values('SGE101007121611', 'Matt Zulawski')
INSERT INTO @tblControlLogAssociates Values('SGE101007121918', 'Thaddeus Edwards')
INSERT INTO @tblControlLogAssociates Values('SGE101007121918', 'James Birming')
INSERT INTO @tblControlLogAssociates Values('SGE101007122842', 'Kenny Turrentine')
INSERT INTO @tblControlLogAssociates Values('SGE101007122842', 'Michael Hirschbine')
INSERT INTO @tblControlLogAssociates Values('SGE101007123229', 'Roland Ajie')
INSERT INTO @tblControlLogAssociates Values('SGE101007123229', 'Rob Wilcox')
INSERT INTO @tblControlLogAssociates Values('SGE101007124251', 'Matt Eddy')
INSERT INTO @tblControlLogAssociates Values('SGE101007124801', 'Chris Swift Jr.')
INSERT INTO @tblControlLogAssociates Values('SGE101007131000', 'Chris Vogel')
INSERT INTO @tblControlLogAssociates Values('SGE101007133410', 'Matt Zulaw')
INSERT INTO @tblControlLogAssociates Values('SGE101007134419', 'Matt Zulaw')
INSERT INTO @tblControlLogAssociates Values('SGE101007134524', 'Scott Gearhart')
INSERT INTO @tblControlLogAssociates Values('SGE101007134623', 'Scott Gearhart')
INSERT INTO @tblControlLogAssociates Values('SGE101007134725', 'Thaddeus Edwards')
INSERT INTO @tblControlLogAssociates Values('SGE101007135219', 'Thaddeus Edwards')
INSERT INTO @tblControlLogAssociates Values('SGE101007141045', 'Kenny Turrentine')
INSERT INTO @tblControlLogAssociates Values('SGE101007141045', 'Matt Zulaw')
INSERT INTO @tblControlLogAssociates Values('SGE101007141045', 'Michael Hirschbine')
INSERT INTO @tblControlLogAssociates Values('SGE101007141656', 'George Samson')
INSERT INTO @tblControlLogAssociates Values('SGE101007141656', 'Rob Wilcox')
INSERT INTO @tblControlLogAssociates Values('SGE101007142441', 'Matt Zulaw')
INSERT INTO @tblControlLogAssociates Values('SGE101007142855', 'James Birming')
INSERT INTO @tblControlLogAssociates Values('SGE101007143208', 'Matt Eddy')
INSERT INTO @tblControlLogAssociates Values('SGE101007143208', 'George Samson')
INSERT INTO @tblControlLogAssociates Values('SGE101007143208', 'Chris Vogel')
INSERT INTO @tblControlLogAssociates Values('SGE101007143208', 'Chris Swift Jr.')
INSERT INTO @tblControlLogAssociates Values('SGE101007143553', 'Thaddeus Edwards')
October 11, 2010 at 3:09 pm
SSSolice (10/11/2010)
Thanks for your reply Drew. I did remove the redundant call to tblControlLog but the performance against the presentation data is still too sluggish. Any help with a new perspective is appreciated.I've split out the two tables for a proper simulation.
That's better, but it's still not representative of your data. Your main prod table has fields called strReceiptNo, dteCompleted, and guidControlLogID, but your main sample table has Receipt and XDate. (It also contains duplicates. Is that really representative of your production data?) Yes, it's easy to map Receipt to strReceiptNo and XDate to dteCompleted, but there is no easy way to create the guidControlLogID which is the column used to link the main table to the secondary table.
Your secondary sample table has similar problems.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 11, 2010 at 3:29 pm
For purposes of this exercise, the guids are irrelevant. Trying to keep it simple but have too many irons in the fire. I appreciate you sticking with me Drew. The @tblControlLog is a one-to-many with @tblControlLogAssociates. Here is the @tblControlLog minus the duplicates.
One receipt here, and potentially many receipts in the associates.
The 2 columns of desired output would be like: 'ReceiptABC', 'Associate1, Associate3, Associate12'
without using FOR XML PATH.
Thanks again.
DECLARE @tblControlLog TABLE (Receipt NVARCHAR(50), XDate NVARCHAR(20))
INSERT INTO @tblControlLog Values('SGE101007063342', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081032', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081119', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081205', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081241', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081316', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007081433', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007082348', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007083149', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007090359', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007090535', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007091710', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007092628', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007093245', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007093649', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007102107', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007102143', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007102221', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007102305', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007103749', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007105436', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007110638', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007111202', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007112118', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007112836', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007115233', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007120347', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007121611', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007121918', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007122842', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007123229', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007124251', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007124801', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007131000', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007133410', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007134419', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007134524', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007134623', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007134725', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007135219', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007141045', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007141656', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007142441', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007142855', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007143208', '10/07/2010')
INSERT INTO @tblControlLog Values('SGE101007143553', '10/07/2010')
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply