x

How can query to get the result?

I have a table1 with

  id   typeid  
  21    1  

table2:

 typeid  nid  
 1       1  
 1       2  

table3:

 nid   name  
 1     groupa  
 2     groupb  

I need the query result as

 id   name  
 21   groupa,groupb

Can anyone help me out?

more ▼

asked Oct 13, 2010 at 03:24 PM in Default

avatar image

Anand
61 4 5 6

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

What you are wanting is a join followed by a concatenation. Jeff Moden provides several examples of how to do this along with their performance implications in: Performance Tuning: Concatenation Functions and Some Tuning Myths ( http://www.sqlservercentral.com/articles/Test+Data/61572/ ). The last option he presents is normally the best.

Edit: I decided to go ahead and write a sample, but I have not tested this:

 with cte1 as (
  select t1.id, t3.name
  from table1 t1
  join table2 t2
   on t1.typeid = t2.typeid
  join table3 t3
   on t2.nid = t3.nid
 )
 
 SELECT c1.id,        
 STUFF((SELECT ','+c2.[name]
                  FROM cte1 c2 
                 WHERE c1.id = c2.id FOR XML PATH('')),1,1,'') as [name]   
 FROM cte1 c1  
 GROUP BY c1.id

Again, I have not tested that, but it should be close to what you are asking for.

Edit2: Corrected the sample to use the third table. I still have not tested it. Thanks WilliamD.

more ▼

answered Oct 13, 2010 at 03:49 PM

avatar image

TimothyAWiseman
15.6k 22 55 38

Timothy wrote:

"with cte1 as ( select t1.id, t2.name from table1 t1 join table2 t2 on t1.id = t2.nid )"

Timothy, I am not sure why you are pulling t2.name in the ctel section of code when table 2 does not contain the name field, only table 3 does. Is this section of code doing something I am missing?

As for the rest of the code, it looks like it would work but I am unfamiliar with this approach.

Peg.

Oct 13, 2010 at 07:24 PM LeDell

@LeDell - Timothy seems to have missed out the 3rd table in his CTE. You would need to add the join from table2 to table3 on column nid to get the name.

The rest of the query will work as advertised.

Oct 14, 2010 at 12:13 AM WilliamD

@William, you are entirely right. Thanks for pointing it out. I guess I should have tested it after all.

Oct 14, 2010 at 06:30 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

with out using CTE


SELECT ID,STUFF((SELECT ','+NAME AS 'data()'
          FROM Table3 t3
          INNER JOIN Table2 t2
          ON t3.nid = t2.nid
          WHERE t2.TypeID = t1.typeID
          FOR XML PATH('')),1,1,'')
FROM Table1 t1

more ▼

answered Oct 14, 2010 at 12:19 AM

avatar image

Cyborg
10.8k 37 57 51

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1095

asked: Oct 13, 2010 at 03:24 PM

Seen: 520 times

Last Updated: Oct 13, 2010 at 03:44 PM

Copyright 2018 Redgate Software. Privacy Policy