Problem setting up concatenation using multiple FOR XML PATH statements

Question

I am trying to query an SCCM DB for Processor Revision, and use FOR XML PATH in two fields to concatenate all the CPU Names, and all the associated computer models.  Trying to get it working with just ONE Processor Revision, first, the data for that is:

NAME,Revision0,Model0,COUNT
Intel(R) Pentium(R) 4 CPU 3.20GHz,1541,HP Compaq dc7600 Convertible Minitower,1
Intel(R) Pentium(R) 4 CPU 3.20GHz,1541,HP Compaq dx2300 Microtower,1
Intel(R) Pentium(R) 4 CPU 3.20GHz,1541,OptiPlex 745,47
Intel(R) Pentium(R) 4 CPU 3.40GHz,1541,HP Compaq dc5700 Microtower,31
Intel(R) Pentium(R) D CPU 3.00GHz,1541,OptiPlex 745,71
Intel(R) Pentium(R) D CPU 3.20GHz,1541,OptiPlex 745,1
Intel(R) Pentium(R) D CPU 3.40GHz,1541,HP xw4400 Workstation,3
Intel(R) Pentium(R) D CPU 3.40GHz,1541,OptiPlex 745,84

I created the code below to use FOR XML PATH on the CPU Name and it concatenates fine. I am having problems with the second FOR XML PATH to concatenate the Computer Model Names, and not sure how to fix it (I think the problem is in the WHERE Statement):

SELECT GPRO.Revision0 AS 'REVISION'
,STUFF((SELECT DISTINCT(', ' + (LTRIM(RTRIM(GPRO1.Name0))))
	FROM dbo.v_GS_PROCESSOR GPRO1
	WHERE GPRO1.Revision0 = GPRO.Revision0
	GROUP BY (', ' + (LTRIM(RTRIM(GPRO1.Name0))))
	ORDER BY (', ' + (LTRIM(RTRIM(GPRO1.Name0))))
	FOR XML PATH(''), TYPE
	).value('.', 'varchar(max)'), 1, 2, '') AS 'CPU NAME' 
,STUFF((SELECT DISTINCT(', ' + (LTRIM(RTRIM(CSYS1.Model0))))
	FROM dbo.v_GS_COMPUTER_SYSTEM CSYS1
		JOIN dbo.v_GS_PROCESSOR GPRO1 ON CSYS1.ResourceID 
		= (SELECT GPRO1.ResourceID WHERE GPRO1.Revision0 = GPRO.Revision0)
	GROUP BY (', ' + (LTRIM(RTRIM(CSYS1.Model0))))
	ORDER BY (', ' + (LTRIM(RTRIM(CSYS1.Model0))))
	FOR XML PATH(''), TYPE
	).value('.', 'varchar(max)'), 1, 2, '') AS 'MODEL' 
, COUNT(GPRO.ResourceID) AS 'COUNT'

FROM dbo.v_GS_PROCESSOR GPRO 
	JOIN dbo.v_GS_COMPUTER_SYSTEM CSYS ON GPRO.ResourceID = CSYS.ResourceID
	JOIN dbo.v_R_System SYS ON GPRO.ResourceID = SYS.ResourceID

WHERE GPRO.GroupID = 1
	AND SYS.Client0 = '1'
	AND SYS.Obsolete0 = '0'
	AND SYS.Operating_System_Name_and0 LIKE '%workstation%'
	AND GPRO.Revision0 = '1541'

GROUP BY GPRO.Revision0

ORDER BY GPRO.Revision0, 'COUNT'

I appreciate any advice, and thanks in advance!

Answer 1

Your code is quite strange. Can you post DDL of your tables with INSERT statements so we can create a query for you? Without this, I'll try to fix the problem this way:

SELECT GPRO.Revision0 AS [REVISION]
,STUFF((SELECT DISTINCT(', ' + LTRIM(RTRIM(GPRO1.Name0)))
	FROM dbo.v_GS_PROCESSOR GPRO1
	WHERE GPRO1.Revision0 = GPRO.Revision0
	
	ORDER BY (', ' + (LTRIM(RTRIM(GPRO1.Name0))))
	FOR XML PATH(''), TYPE
	).value('.', 'varchar(max)'), 1, 2, '') AS [CPU NAMES] 
,STUFF((SELECT DISTINCT(', ' + LTRIM(RTRIM(CSYS1.Model0))
	FROM dbo.v_GS_COMPUTER_SYSTEM CSYS1
		JOIN dbo.v_GS_PROCESSOR GPRO2 ON CSYS1.ResourceID 
		= GPRO2.ResourceID WHERE GPRO2.Revision0 = GPRO.Revision0
	ORDER BY (', ' + (LTRIM(RTRIM(CSYS1.Model0))))
	FOR XML PATH(''), TYPE
	).value('.', 'varchar(max)'), 1, 2, '') AS [MODELS] 
, COUNT(GPRO.ResourceID) AS [COUNT]

FROM dbo.v_GS_PROCESSOR GPRO 
	JOIN dbo.v_GS_COMPUTER_SYSTEM CSYS ON GPRO.ResourceID = CSYS.ResourceID
	JOIN dbo.v_R_System SYS ON GPRO.ResourceID = SYS.ResourceID

WHERE GPRO.GroupID = 1
	AND SYS.Client0 = '1'
	AND SYS.Obsolete0 = '0'-- ? is it a character column
	AND SYS.Operating_System_Name_and0 LIKE '%workstation%'
	AND GPRO.Revision0 = '1541' -- is it a character?

GROUP BY GPRO.Revision0

ORDER BY GPRO.Revision0, [COUNT]

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Hi, Naomi--yeah, the code got kinda convoluted, I used the example from here:http://stackoverflow.com/questions/6074321/sql-for-xml-path-list-and-count on the first FOR XML PATH sub-query, and had to do all the TRIM and parenthesis as there were non ASCII characters in the names, and I had to use DISTINCT to avoid duplicate names (as you can see they exist in the original source data I sent).

I'm just using the standard Views on SCCM 2007 SP2 R3, SQL 2008 DB, not sure how to create and send you a DDL?  As to your two questions, BOTH Obsolete0 and Revision0 are (int, null).

Using the first sub-query alone, I DO get the desired output (from my first post's data, the CPU Names concatenate into just the 5 different ones, not the 8 total in the data) but to get it I had to add in the GROUP BY statement:

REVISION	CPU NAME	COUNT
1541	Intel(R) Pentium(R) 4 CPU 3.20GHz, Intel(R) Pentium(R) 4 CPU 3.40GHz, Intel(R) Pentium(R) D CPU 3.00GHz, Intel(R) Pentium(R) D CPU 3.20GHz, Intel(R) Pentium(R) D CPU 3.40GHz	239

Adding in the second sub-query, it's not working right, and causing EACH individual model to list (with a count of 1), rather than concatenating and aggregating things.  I think (and why I did the code that way in the second sub-query) that somehow I have to tie the ResourceID of the Computer View back to the Processor Revision, so that I get the computer names for that revision...

Thanks!

		COUNT
		239

Answer 3

If you're using integer columns, then use integer constants (don't put them in single quotes) when you're doing comparison.

Did you try my query? Are you getting wrong results with it?

To create DDL of the tables you can right click on the table name in SSMS and chose SCRIPT, create.

But in addition to DDL we also need insert statements for some data and desired result based on the data.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Also, if one query works correctly, then try the first one and put it into temp table and then concatenate the second column using the temp table.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog