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
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
Dang it... thanks for the catch, Lynn...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply