WoodyGuthrie
MIS
I posted this problem over at [a href="[URL unfurl="true"]http://www.sqlservercentral.com/Forums/Topic1002255-338-1.aspx"[/URL]]SQL Server Central[/a] but had problems with my data and framing the problem so I try it here. I appreciate any help.
I need to bring up the "many" attribute to a single row and into a csv column without using the FOR XML PATH method.
This method does provide the desired result but is very very slow.
Using the methos above is extremely inefficient on large data sets.
Here is the sample data for the ONE table.
Here is the sample data for the MANY table.
I know this is not correct, but here is what I'm working with at the moment.
This is the desired result
Any help is greatly appreciated!
Woody
I need to bring up the "many" attribute to a single row and into a csv column without using the FOR XML PATH method.
This method does provide the desired result but is very very slow.
Code:
SELECT
cl.Receipt,
cl.XDATE,
STUFF((SELECT
', ' + CAST(cla.Associates AS VARCHAR(1000))
FROM
@tblControlLogAssociates AS cla
WHERE
cl.Receipt = cla.Receipt
FOR
XML PATH('')), 1, 1, '') AS [Associates]
FROM
@tblControlLog AS cl;Using the methos above is extremely inefficient on large data sets.
Here is the sample data for the ONE table.
Code:
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')Here is the sample data for the MANY table.
Code:
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')I know this is not correct, but here is what I'm working with at the moment.
Code:
SELECT
cl.Receipt
, cl.XDate
, cla.Assoc
FROM
(
SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Receipt ASC)
, cl.Receipt
, cl.XDate
FROM @tblControlLog as cl
) cl
LEFT JOIN
(
SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Receipt, Associates ASC)
, cla.Receipt
, cla.Associates AS Assoc
FROM @tblControlLogAssociates cla
) cla
ON cl.Receipt = cla.Receipt
--AND cl.RowNumber <= cla.RowNumber
GROUP BY cl.Receipt, cl.XDate , cla.Assoc
ORDER BY cl.Receipt, cl.XDateThis is the desired result
Code:
Receipt XDATE Associates
SGE101007063342 10/07/2010 Matt Eddy, Scott Gearhart
SGE101007081032 10/07/2010 Chris Vogel, Matt Zulaw, Rob Wilcox
SGE101007081119 10/07/2010 James Birming
SGE101007081205 10/07/2010 Chris Swift Jr., Demetrice Tisdale, jerome Barber
SGE101007081241 10/07/2010 Matt Eddy
SGE101007081316 10/07/2010 Eric Bouter, Chris Swanson
SGE101007081433 10/07/2010 George Samson, Roland Ajie, Michael Hirschbine
SGE101007082348 10/07/2010 Matt Eddy
SGE101007083149 10/07/2010 Wally Bohlen Jr, Marion Franks
SGE101007090359 10/07/2010 Thaddeus Edwards, Kenny Turrentine, James Birming
SGE101007090535 10/07/2010 Scott Gearhart
SGE101007091710 10/07/2010 Demetrice Tisdalev
SGE101007092628 10/07/2010 Michael Hirschbine, George Samson, Chris Swanson
SGE101007093245 10/07/2010 Kenny Turrentine
SGE101007093649 10/07/2010 Chris Swift Jr., Rob Wilcox
SGE101007102107 10/07/2010 Wally Bohlen Jr
SGE101007102143 10/07/2010 Rob Wilcox
SGE101007102221 10/07/2010 Roland Ajie, Chris Vogel, Matt Zulaw
SGE101007102305 10/07/2010 Chris Swift Jr.
SGE101007103749 10/07/2010 jerome Barber
SGE101007105436 10/07/2010 Michael Hirschbine
SGE101007110638 10/07/2010 Michael Hirschbine
SGE101007111202 10/07/2010 Kenny Turrentine
SGE101007112118 10/07/2010 Demetrice Tisdale
SGE101007112836 10/07/2010 Wally Bohlen Jr
SGE101007115233 10/07/2010 James Birming, Thaddeus Edwards, Matt Eddy
SGE101007120347 10/07/2010 Demetrice Tisdale, eorge Samson, Chris Vogel
SGE101007121611 10/07/2010 Matt Zulawski
SGE101007121918 10/07/2010 Thaddeus Edwards, James Birming
SGE101007122842 10/07/2010 Kenny Turrentine, Michael Hirschbine
SGE101007123229 10/07/2010 Roland Ajie, Rob Wilcox
SGE101007124251 10/07/2010 Matt Eddy
SGE101007124801 10/07/2010 Chris Swift Jr.
SGE101007131000 10/07/2010 Chris Vogel
SGE101007133410 10/07/2010 Matt Zulaw
SGE101007134419 10/07/2010 Matt Zulaw
SGE101007134524 10/07/2010 Scott Gearhart
SGE101007134623 10/07/2010 Scott Gearhart
SGE101007134725 10/07/2010 Thaddeus Edwards
SGE101007135219 10/07/2010 Thaddeus Edwards
SGE101007141045 10/07/2010 Kenny Turrentine, Matt Zulaw, Michael Hirschbine
SGE101007141656 10/07/2010 George Samson, Rob Wilcox
SGE101007142441 10/07/2010 Matt Zulaw
SGE101007142855 10/07/2010 James Birming
SGE101007143208 10/07/2010 Matt Eddy, George Samson, Chris Vogel, Chris Swift Jr.
SGE101007143553 10/07/2010 Thaddeus EdwardsAny help is greatly appreciated!
Woody