November 14, 2012 at 3:41 am
Can someone help I have a peice of code that that i am trying to use to oull som information into a report. Unfortunaltey due to nature of the database when certain feild are selected within the app i produces multiple rows of data in the report. I am trying to merge the column TITLE by the GROUP BY of REF_NO (The title field could have any number of references in it but for this example it brings through two. Also there will be more than 1 REF_NO to deal with to.)
Here is the example code.
SELECT dbo.SU_EXTENSION_DATA.REF_NO, dbo.AR_PRIMARY_ASSET.TITLE
FROM dbo.SU_EXTENSION_DATA INNER JOIN
dbo.AR_PRIMARY_ASSET ON dbo.SU_EXTENSION_DATA.VALUE_REF = dbo.AR_PRIMARY_ASSET.ITEM_REF
WHERE (dbo.SU_EXTENSION_DATA.EXTENSION_FIELD_REF = 501192) AND (dbo.SU_EXTENSION_DATA.CORE_ENTITY = 2) AND
(dbo.SU_EXTENSION_DATA.REF_NO = 86503)
And here are the results
REF_NOTITLE
86503VSM
86503AD Access
I would like it to show
REF_NO TITLE
86503 VSM, AD ACCESS
Can anyone help?
November 14, 2012 at 3:50 am
have some sample data here
November 14, 2012 at 3:51 am
Google the following:
Concatenate FOR XML PATH
November 14, 2012 at 3:52 am
This article explains various methods to concatenate row values:
Concatenating Row Values in Transact-SQL[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2012 at 4:53 am
I have tried 2 different ways now and each data row comes out with the exact same data in. It seems to be picking up the all the 'TITLE' and placing all of them in the new 'Products' column.
Here is the most recent code i changed and used.
SELECT REF_NO,
stuff( (SELECT ','+TITLE
FROM bgc_MERGE
WHERE ref_no = REF_NO
ORDER BY REF_NO
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,1,'')
AS Products
FROM bgc_MERGE
GROUP BY ref_NO ;
There are hundreds of REF_NO that this needs to work on when this query runs but every row for every distinct ref_no comes out the same.
HELP 🙂
November 14, 2012 at 4:56 am
also used this and it produces the same.
SELECT DISTINCT REF_NO,
(SELECT TITLE + ','
FROM bgc_MERGE
WHERE REF_NO = REF_NO
ORDER BY TITLE FOR XML PATH('')) AS HARDWARE
FROM bgc_MERGE
GROUP BY REF_NO;
November 14, 2012 at 5:11 am
pmercer (11/14/2012)
I have tried 2 different ways now and each data row comes out with the exact same data in. It seems to be picking up the all the 'TITLE' and placing all of them in the new 'Products' column.Here is the most recent code i changed and used.
SELECT REF_NO,
stuff( (SELECT ','+TITLE
FROM bgc_MERGE
WHERE ref_no = REF_NO
ORDER BY REF_NO
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,1,'')
AS Products
FROM bgc_MERGE
GROUP BY ref_NO ;
There are hundreds of REF_NO that this needs to work on when this query runs but every row for every distinct ref_no comes out the same.
HELP 🙂
Post DDL and sample data + desired output. (as outlined in the first link in my signature)
Without that it will be too difficult to guess what you actually want.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2012 at 5:28 am
Not sure what DDL info you need
Here is some sample Data
REF_NOTITLE
25995bsdinfg0503.eu.ad.espeed.com
25995ESSHQSM01.eu.ad.espeed.com
25995ukqsmweb01.eu.ad.espeed.com
26237ukesp024.eu.ad.espeed.com
26237ukqsmweb01.eu.ad.espeed.com
26258ukqsmweb01.eu.ad.espeed.com
26267esshcddc03.eu.ad.espeed.com
26267ukqsmweb01.eu.ad.espeed.com
26872ukesp024.eu.ad.espeed.com
45287ukesp024.eu.ad.espeed.com
I would like it to read.
REF_NOTITLE
25995bsdinfg0503.eu.ad.espeed.com, ESSHQSM01.eu.ad.espeed.com, ukqsmweb01.eu.ad.espeed.com
26237ukesp024.eu.ad.espeed.com, ukqsmweb01.eu.ad.espeed.com
26258ukqsmweb01.eu.ad.espeed.com
26267esshcddc03.eu.ad.espeed.com, ukqsmweb01.eu.ad.espeed.com
26872ukesp024.eu.ad.espeed.com
45287ukesp024.eu.ad.espeed.com
Not sure if this helps. The code in my previous replies has applied to this.
Apologies for my lack of information i am new to SQL and to this forum.
November 14, 2012 at 5:31 am
...
Apologies for my lack of information i am new to SQL and to this forum.
The following will get you started receiving relevant and fast responses on this forum:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 14, 2012 at 8:23 am
So created this test table for people to help me on.
Here is the code.
[Code]
CREATE TABLE dbo.TEST_TABLE2(
[REF_NO] [int] NULL,
[TITLE] [varchar](max) NULL
) ON [PRIMARY]
INSERT INTO dbo.TEST_TABLE2
Values (25995, 'Outlook') ;
INSERT INTO dbo.TEST_TABLE2
Values (25995, 'VSM') ;
INSERT INTO dbo.TEST_TABLE2
Values (25995, 'PC') ;
INSERT INTO dbo.TEST_TABLE2
Values (26237, 'Word') ;
INSERT INTO dbo.TEST_TABLE2
Values (26237, 'ukqsmweb01') ;
INSERT INTO dbo.TEST_TABLE2
Values (26258, 'Driver');
INSERT INTO dbo.TEST_TABLE2
Values (26267, 'Chrome');
INSERT INTO dbo.TEST_TABLE2
Values (26267, 'IE');
INSERT INTO dbo.TEST_TABLE2
Values (26872,'Firefox' );
INSERT INTO dbo.TEST_TABLE2
Values (45287, 'VSM');
[/code]
I the created a view based on this code.
SELECT REF_NO, stuff
((SELECT ',' + TITLE
FROM dbo.TEST_TABLE2
WHERE REF_NO = REF_NO
ORDER BY TITLE FOR XML PATH(''), TYPE ).value('.', 'varchar(max)'), 1, 1, '') AS HARDWARE
FROM dbo.TEST_TABLE2
GROUP BY REF_NO;
And it brings these results.
REF_NOHARDWARE
25995Chrome,Driver,Firefox,IE,Outlook,PC,ukqsmweb01,VSM,VSM,Word
26237Chrome,Driver,Firefox,IE,Outlook,PC,ukqsmweb01,VSM,VSM,Word
26258Chrome,Driver,Firefox,IE,Outlook,PC,ukqsmweb01,VSM,VSM,Word
26267Chrome,Driver,Firefox,IE,Outlook,PC,ukqsmweb01,VSM,VSM,Word
26872Chrome,Driver,Firefox,IE,Outlook,PC,ukqsmweb01,VSM,VSM,Word
45287Chrome,Driver,Firefox,IE,Outlook,PC,ukqsmweb01,VSM,VSM,Word
when i would like it to look like this
REF_NOTITLE
25995Outlook, VSM, PC
26237Word, ukqsmweb01
26258Driver
26267Chrome, IE
26872Firefox
45287VSM
please be aware the REF_NO refers to Incident numbers therefore the amount of them will increase over time.
If there is anything else anyone needs let me know. Thanks in advance
November 14, 2012 at 8:32 am
See, the article I've pointed you to, makes the difference!
Your problem is not using table aliases! Your sub query concatenates all rows from dbo.TEST_TABLE2 because your filter it using "WHERE REF_NO = REF_NO" - which is the same as if you would do "WHERE 1=1"
Try this:
SELECT T1.REF_NO, stuff
((SELECT ',' + T2.TITLE
FROM dbo.TEST_TABLE2 AS T2
WHERE T2.REF_NO = T1.REF_NO
ORDER BY T2.TITLE FOR XML PATH(''), TYPE ).value('.', 'varchar(max)'), 1, 1, '') AS HARDWARE
FROM dbo.TEST_TABLE2 AS T1
GROUP BY T1.REF_NO;
November 14, 2012 at 9:09 am
Thank you very much i was racking my brains and it all clicks now.
Thank you again.
:-):-):-):-):-):-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply