retrieve more than one row in one row
-
Hi, I have an sql query that retrieves data for some ID where this ID has more than one data related to it. Example: ID Symbol 01 A 01 B 01 C of course the primary key is (ID, Symbol). when you select the ID and Symbol, it retieves the previous three rows. I need to retrieve these data in one row by concatinating the Symbol to itself; i.e: I need the result to be just like the following: ID Symbol 01 A and B and C Is this possible???
Kind Regards OBarahmeh
-
Hi, I have an sql query that retrieves data for some ID where this ID has more than one data related to it. Example: ID Symbol 01 A 01 B 01 C of course the primary key is (ID, Symbol). when you select the ID and Symbol, it retieves the previous three rows. I need to retrieve these data in one row by concatinating the Symbol to itself; i.e: I need the result to be just like the following: ID Symbol 01 A and B and C Is this possible???
Kind Regards OBarahmeh
You could do this using a cursor[^] to loop through the rows and build up the string. I wouldn't advise it for large resultsets as you could end up with performance issues.
Declan Bright www.declanbright.com
-
Hi, I have an sql query that retrieves data for some ID where this ID has more than one data related to it. Example: ID Symbol 01 A 01 B 01 C of course the primary key is (ID, Symbol). when you select the ID and Symbol, it retieves the previous three rows. I need to retrieve these data in one row by concatinating the Symbol to itself; i.e: I need the result to be just like the following: ID Symbol 01 A and B and C Is this possible???
Kind Regards OBarahmeh
Yes, it is possible :)
CREATE TABLE #T (ID varchar(2), Symbol varchar(1));
INSERT INTO #T(ID, Symbol) SELECT '01', 'A'
INSERT INTO #T(ID, Symbol) SELECT '01', 'B'
INSERT INTO #T(ID, Symbol) SELECT '01', 'C'
INSERT INTO #T(ID, Symbol) SELECT '02', 'B'
INSERT INTO #T(ID, Symbol) SELECT '02', 'D'SELECT
ID,
STUFF(
(SELECT ' and ' + Symbol FROM #T WHERE ID = t.ID FOR XML PATH(''))
, 1
, 5
, ''
) AS Symbols
FROM #T AS t
GROUP BY IDDROP TABLE #T
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
-
Hi, I have an sql query that retrieves data for some ID where this ID has more than one data related to it. Example: ID Symbol 01 A 01 B 01 C of course the primary key is (ID, Symbol). when you select the ID and Symbol, it retieves the previous three rows. I need to retrieve these data in one row by concatinating the Symbol to itself; i.e: I need the result to be just like the following: ID Symbol 01 A and B and C Is this possible???
Kind Regards OBarahmeh
SELECT SymbolA.Id
,Symbol=SymbolA.Symbol + ' and ' + SymbolB.Symbol + ' and ' + SymbolC.Symbol
FROM Symbol SymbolA
INNER JOIN Symbol SymbolB
ON SymbolA.ID=SymbolB.ID
AND SymbolB.Symbol='B'
INNER JOIN Symbol SymbolC
ON SymbolA.ID=SymbolC.ID
AND SymbolC.Symbol='C'
WHERE SymbolA.Symbol='A' -
SELECT SymbolA.Id
,Symbol=SymbolA.Symbol + ' and ' + SymbolB.Symbol + ' and ' + SymbolC.Symbol
FROM Symbol SymbolA
INNER JOIN Symbol SymbolB
ON SymbolA.ID=SymbolB.ID
AND SymbolB.Symbol='B'
INNER JOIN Symbol SymbolC
ON SymbolA.ID=SymbolC.ID
AND SymbolC.Symbol='C'
WHERE SymbolA.Symbol='A' -
Well... Let's just hope he is not using the whole alphabet for the Symbol field :)
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
That's not in the scope of the problem as presented. :-D
-
Yes, it is possible :)
CREATE TABLE #T (ID varchar(2), Symbol varchar(1));
INSERT INTO #T(ID, Symbol) SELECT '01', 'A'
INSERT INTO #T(ID, Symbol) SELECT '01', 'B'
INSERT INTO #T(ID, Symbol) SELECT '01', 'C'
INSERT INTO #T(ID, Symbol) SELECT '02', 'B'
INSERT INTO #T(ID, Symbol) SELECT '02', 'D'SELECT
ID,
STUFF(
(SELECT ' and ' + Symbol FROM #T WHERE ID = t.ID FOR XML PATH(''))
, 1
, 5
, ''
) AS Symbols
FROM #T AS t
GROUP BY IDDROP TABLE #T
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
I'm always amazed at the lack of functions in SQL Server. There should be a JOIN CONCAT aggregate function. There should also be TRIM (not just LTRIM and RTRIM), and PADding functions. Ah, well, an opportunity to write my first CLR aggregate function! :-D Ha! The example for creating a CLR aggregate is Concatenate! :laugh: Whoo hoo! Copied the example and tweaked it a little.
SELECT ID
,Symbol=STUFF(dbo.Concatenate(' and '+Symbol),1,5,'')
FROM Symbol
GROUP BY ID
ORDER BY ID
,Symbolmodified on Thursday, July 31, 2008 11:33 AM
-
I'm always amazed at the lack of functions in SQL Server. There should be a JOIN CONCAT aggregate function. There should also be TRIM (not just LTRIM and RTRIM), and PADding functions. Ah, well, an opportunity to write my first CLR aggregate function! :-D Ha! The example for creating a CLR aggregate is Concatenate! :laugh: Whoo hoo! Copied the example and tweaked it a little.
SELECT ID
,Symbol=STUFF(dbo.Concatenate(' and '+Symbol),1,5,'')
FROM Symbol
GROUP BY ID
ORDER BY ID
,Symbolmodified on Thursday, July 31, 2008 11:33 AM
-
I don't know why you people are so scared of XML :)
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
Who is? :confused: