November 3, 2008 at 3:44 am
Hi all,
I have two tables with one to many relationship. Can i get the line table data separated by comma within a single select statement in sql 2005.
Eg:
Table 1:
A B
-------
1 XX
2 YY
Table 2:
C D
---------
1 P
1 Q
1 R
2 S
2 T
I need the below output with single sql select statement:
A B Result
----------------------------
1 XX P,Q,R
2 YY S,T
Is this possible.. Kindly clarify..
Thanks & Regards,
Sridevi
November 3, 2008 at 5:13 am
Sridevi (11/3/2008)
Hi all,I have two tables with one to many relationship. Can i get the line table data separated by comma within a single select statement in sql 2005.
Eg:
Table 1:
A B
-------
1 XX
2 YY
Table 2:
C D
---------
1 P
1 Q
1 R
2 S
2 T
I need the below output with single sql select statement:
A B Result
----------------------------
1 XX P,Q,R
2 YY S,T
Is this possible.. Kindly clarify..
Thanks & Regards,
Sridevi
yes this is possible, let me know the query so that i could modify it
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 3, 2008 at 5:33 am
CREATE TABLE Table1(A INT, B VARCHAR(10))
GO
CREATE TABLE TABLE2(C INT, D INT, E VARCHAR(10))
GO
Table1 DATA:
A B
------
1 XX
2 YY
Table2 DATA:
C D E
--------------
1 1 P
1 2 Q
1 3 R
2 1 S
2 2 T
My QUERY:
SELECT TABLE1.A, TABLE1.B,TABLE2.C,TABLE2.D,TABLE2.E FROM
TABLE1 A JOIN TABLE2 B ON A.A = B.C AND A.A = 1
RESULT COMES AS:
A B C D E
-------------------
1 XX 1 1 P
1 XX 1 2 Q
1 XX 1 3 R
Instead of three rows, i need the result as below:
A B C D E
----------------------------
1 XX 1 1,2,3 P,Q,R
Is this can be done without using cursor/loop?
Regards,
Sridevi
November 3, 2008 at 5:43 am
Sridevi (11/3/2008)
CREATE TABLE Table1(A INT, B VARCHAR(10))GO
CREATE TABLE TABLE2(C INT, D INT, E VARCHAR(10))
GO
Table1 DATA:
A B
------
1 XX
2 YY
Table2 DATA:
C D E
--------------
1 1 P
1 2 Q
1 3 R
2 1 S
2 2 T
My QUERY:
SELECT TABLE1.A, TABLE1.B,TABLE2.C,TABLE2.D,TABLE2.E FROM
TABLE1 A JOIN TABLE2 B ON A.A = B.C AND A.A = 1
RESULT COMES AS:
A B C D E
-------------------
1 XX 1 1 P
1 XX 1 2 Q
1 XX 1 3 R
Instead of three rows, i need the result as below:
A B C D E
----------------------------
1 XX 1 1,2,3 P,Q,R
Is this can be done without using cursor/loop?
Regards,
Sridevi
Loop is required for such sort of output, why dont you create a function that performs this on the desired columns
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 3, 2008 at 5:57 am
Many Thanks to you.
I wrote a function and i got the result too.
CREATE FUNCTION select_concat (@A INT )
RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @p VARCHAR(MAX) ;
SET @p = '' ;
SELECT @p = @p + D + ','
FROM TABLE2
WHERE C = @A;
RETURN @p
END
SELECT A,dbo.select_concat(A)
FROM TABLE1
GROUP BY A
Thanks again.
Sridevi
November 3, 2008 at 9:55 pm
Sridevi (11/3/2008)
Many Thanks to you.I wrote a function and i got the result too.
CREATE FUNCTION select_concat (@A INT )
RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @p VARCHAR(MAX) ;
SET @p = '' ;
SELECT @p = @p + D + ','
FROM TABLE2
WHERE C = @A;
RETURN @p
END
SELECT A,dbo.select_concat(A)
FROM TABLE1
GROUP BY A
Thanks again.
Sridevi
Heh... is the trailing comma that leaves ok with you? If not, try this, instead...
CREATE FUNCTION select_concat (@A INT )
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @p VARCHAR(MAX) ;
SELECT @p = COALESCE(@p +',') + D
FROM dbo.TABLE2
WHERE C = @A;
RETURN @p
END
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2008 at 9:59 pm
... and, before you get all fired up and start using that or any other concatenation function, you have to read the following to avoid some serious performance problems...
http://www.sqlservercentral.com/articles/Test+Data/61572/
It also has an alternate method that uses "STUFF" that works really well.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2008 at 10:01 pm
Jeff Moden (11/3/2008)
Sridevi (11/3/2008)
Many Thanks to you.I wrote a function and i got the result too.
CREATE FUNCTION select_concat (@A INT )
RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @p VARCHAR(MAX) ;
SET @p = '' ;
SELECT @p = @p + D + ','
FROM TABLE2
WHERE C = @A;
RETURN @p
END
SELECT A,dbo.select_concat(A)
FROM TABLE1
GROUP BY A
Thanks again.
Sridevi
Heh... is the trailing comma that leaves ok with you? If not, try this, instead...
CREATE FUNCTION select_concat (@A INT )
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @p VARCHAR(MAX) ;
SELECT @p = COALESCE(@p +',') + D
FROM dbo.TABLE2
WHERE C = @A;
RETURN @p
END
Or this:
CREATE FUNCTION select_concat (@A INT )
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @p VARCHAR(MAX) ;
SELECT
@p = COALESCE(@p +',', '') + D
FROM
dbo.TABLE2
WHERE
C = @A;
RETURN @p
END
November 4, 2008 at 8:28 am
How about this approach? It may be faster than using an inline function.
[font="Courier New"]--create temp tables
CREATE TABLE #Table1
(
A INT
,B VARCHAR(10)
)
CREATE TABLE #Table2
(
C INT
,D INT
,E VARCHAR(10)
)
--insert sample data into our tables
INSERT INTO #Table1
SELECT 1, 'XX'
UNION ALL SELECT 2, 'YY'
INSERT INTO #Table2
SELECT 1, 1, 'P'
UNION ALL SELECT 1, 2, 'Q'
UNION ALL SELECT 1, 3, 'R'
UNION ALL SELECT 2, 1, 'S'
UNION ALL SELECT 2, 2, 'T'
--create a CTE of the tables joined
-- this will allow us to join the CTE to itself to create our data
;WITH All_Data AS
(
SELECT A.A
,A.B
,B.C
,B.D
,B.E
FROM #TABLE1 A
JOIN #TABLE2 B ON A.A = B.C
)
-- return A, B, C as is
-- group by those columns to get distinct values
-- columns D, E should be CSV
SELECT A
,B
,C
,STUFF((SELECT ',' + CAST(B.D AS VARCHAR)
FROM All_Data B
WHERE A.A = B.A
FOR XML PATH('')),1,1,'') AS D
,STUFF((SELECT ',' + CAST(B.E AS VARCHAR)
FROM All_Data B
WHERE A.A = B.A
FOR XML PATH('')),1,1,'') AS E
FROM All_Data A
WHERE A.A = 1 -- only return data for 1, if you remove this you will get all rows
GROUP BY A
,B
,C
-- clean up
DROP TABLE #TABLE1
DROP TABLE #TABLE2
[/font]
November 4, 2008 at 7:24 pm
Lynn Pettis (11/3/2008)
Jeff Moden (11/3/2008)
Sridevi (11/3/2008)
Many Thanks to you.I wrote a function and i got the result too.
CREATE FUNCTION select_concat (@A INT )
RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @p VARCHAR(MAX) ;
SET @p = '' ;
SELECT @p = @p + D + ','
FROM TABLE2
WHERE C = @A;
RETURN @p
END
SELECT A,dbo.select_concat(A)
FROM TABLE1
GROUP BY A
Thanks again.
Sridevi
Heh... is the trailing comma that leaves ok with you? If not, try this, instead...
CREATE FUNCTION select_concat (@A INT )
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @p VARCHAR(MAX) ;
SELECT @p = COALESCE(@p +',') + D
FROM dbo.TABLE2
WHERE C = @A;
RETURN @p
ENDOr this:
CREATE FUNCTION select_concat (@A INT )
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @p VARCHAR(MAX) ;
SELECT
@p = COALESCE(@p +',', '') + D
FROM
dbo.TABLE2
WHERE
C = @A;
RETURN @p
END
Dang it... thanks for the catch, Lynn...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy