Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Very specific query Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2013 9:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 13,253, Visits: 12,087
kapil_kk (6/26/2013)
you can do the same thing as Sean did using SUBSTRING also:

SELECT id,
SUBSTRING((SELECT ', ' + Term
FROM Something s2
WHERE s1.id = s2.id
and s2.Type = 'Subject'
ORDER BY s2.Term
FOR XML PATH('')), 2, 1000) as Result
FROM Something s1
where Type = 'Subject'
GROUP BY id


The problem here is that you have to choose an arbitrary length. In your case it was 1000 which is plenty to cover the sample data. The issue can be demonstrated by changing the length to something shorter than the output you can see what happens.

Let's change the length to 10 instead 1000.

SELECT id,
Substring((SELECT ', ' + Term
FROM Something s2
WHERE s1.id = s2.id
AND s2.Type = 'Subject'
ORDER BY s2.Term
FOR XML PATH('')), 2, 10) AS Result
FROM Something s1
WHERE Type = 'Subject'
GROUP BY id



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1467725
Posted Wednesday, June 26, 2013 11:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
Sean Lange (6/26/2013)
kapil_kk (6/26/2013)
you can do the same thing as Sean did using SUBSTRING also:

SELECT id,
SUBSTRING((SELECT ', ' + Term
FROM Something s2
WHERE s1.id = s2.id
and s2.Type = 'Subject'
ORDER BY s2.Term
FOR XML PATH('')), 2, 1000) as Result
FROM Something s1
where Type = 'Subject'
GROUP BY id


The problem here is that you have to choose an arbitrary length. In your case it was 1000 which is plenty to cover the sample data. The issue can be demonstrated by changing the length to something shorter than the output you can see what happens.

Let's change the length to 10 instead 1000.

SELECT id,
Substring((SELECT ', ' + Term
FROM Something s2
WHERE s1.id = s2.id
AND s2.Type = 'Subject'
ORDER BY s2.Term
FOR XML PATH('')), 2, 10) AS Result
FROM Something s1
WHERE Type = 'Subject'
GROUP BY id


Yes, Sean changing length can arise problem...thanks for correcting that



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1467960
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse