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

SQL concatenate Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 12:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
I have a SQL question…

Picture 3 tables in this relationship:

Table A – Column1 (PK), Coulmn2 (Identity)
Table B – Column1 (PK), Coulmn2 (Identity) sample data for column 2 is – a_id, c_id
Table C – Column1 (PK), Coulmn2 (Identity) sample data for column 2 is integer information)


A row of A can have 1 to many rows of B.

Each row of B must refer to one row of C

What I want to do is set up this result.

Assume that A.identity =1 and B.c_id refers to C.identity=1 and C.integer_information = 100
Assume that another row of A.identity = 2 and there are there rows of B referring to C.Integer_information of 100,200 and 300.
I need this result:

a.Identity Result of c.integer_information
1 100
2 100,200,300

I have this much SQL

Select
A.identity

From A
Inner join B on B.a_id = a.identity
Inner join C on c.identity = b.c_id

How do I concatenate the values above with no comma if there is 1 value and commas between multiple values?
Post #1430056
Posted Tuesday, March 12, 2013 1:07 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:13 AM
Points: 620, Visits: 864
Don't know that I completely understood the table/data setup but hopefully this at least points you in the right direction

DECLARE @TblA TABLE (
Col1 INT
, Col2 INT
);

DECLARE @TblB TABLE (
Col1 INT
, Col2 INT
);

DECLARE @TblC TABLE (
Col1 INT
, Col2 INT
);

INSERT INTO @TblA (Col1, Col2) VALUES (1, 1),(2, 2);
INSERT INTO @TblC (Col1, Col2) VALUES (100, 1),(200, 2),(300, 3);
INSERT INTO @TblB (Col1, Col2) VALUES (1, 1),(2, 1),(2, 2),(2, 3);

Select A.Col2
, STUFF((
SELECT ',' + CONVERT(VARCHAR,c.Col1)
FROM @TblB B
Inner join @TblC C on c.Col2 = b.Col2
WHERE B.Col1 = a.Col2
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')
From @TblA A



_____________________________________________________________________
- Nate

@nate_hughes
Post #1430069
Posted Tuesday, March 12, 2013 1:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
You have enough points that I can assume you are not around here. Please post ddl, sample data and desired output. I suspect you want a cross tab. You can find some details about that by following the links in my signature.

_______________________________________________________________

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 #1430072
Posted Tuesday, March 12, 2013 1:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
RP_DBA (3/12/2013)
Don't know that I completely understood the table/data setup but hopefully this at least points you in the right direction

Select A.Col2
, STUFF((
SELECT ',' + CONVERT(VARCHAR,c.Col1)
FROM @TblB B
Inner join @TblC C on c.Col2 = b.Col2
WHERE B.Col1 = a.Col2
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')
From @TblA A


+1

This is the way I do concatenation when necessary to do so. I think it's much better than the old COALESCE method because it doesn't need any variables assigned to hold the string as it's built.

But PLEASE don't store any delimited strings in the database. I have some clients who developed their db schema before I got involved and for some reason some people just love to store delimited strings. I guess initially it's easier to do that than to create new tables and add joins to their queries. But then getting the data OUT efficiently is problematic. (Thank you Jeff Moden and others who developled DelimitedSplit8K--if I didn't have that I might have to think about becoming a .NET developer! )

 
Post #1430073
Posted Tuesday, March 12, 2013 2:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
Sean Lange (3/12/2013)
You have enough points that I can assume you are not around here. Please post ddl, sample data and desired output. I suspect you want a cross tab. You can find some details about that by following the links in my signature.


Missing something in this sentence?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1430106
Posted Tuesday, March 12, 2013 2:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
Lynn Pettis (3/12/2013)
Sean Lange (3/12/2013)
You have enough points that I can assume you are not around here. Please post ddl, sample data and desired output. I suspect you want a cross tab. You can find some details about that by following the links in my signature.


Missing something in this sentence?


hehe

Meant to say "new around here". I need a digital proof reader. I actually type quite fast and rarely look at the keys but I still manage to miss words at least half of the time.


_______________________________________________________________

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 #1430111
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse