Concatenating a row
-
I have the following table
BID XER EE 1 EE 2 EE 3 FF 4 FF 5 FF 6 And want to achieve the following output.
BID XER EE 1, 2, 3 DD 4, 5, 6 Please can someone let me know how to do this? (I think I need to use XML path)
- system243trd - Friday, June 30, 2017 4:38 AM
DECLARE @TABLE TABLE
(
BID CHAR(2)
,XER INT
)INSERT INTO @TABLE
VALUES('EE',1),('EE',2),('EE',3),('FF',4),('FF',5),('FF',6)SELECT
BID,
STUFF((
SELECT ','+ CAST(XER AS VARCHAR(3))
FROM @TABLE t1
WHERE t1.BID = t.bid FOR XML PATH('')),1,1,'') AS XER
FROM @TABLE tGROUP BY
BIDIs this what you're after. I've assumed that the change from FF to DD is a typo.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
WITH Jic
AS
(
SELECT BID = 'EE', XER = 1 UNION
SELECT 'EE', 2 UNION
SELECT 'EE', 3 UNION
SELECT 'FF', 4 UNION
SELECT 'FF', 5 UNION
SELECT 'FF', 6),
Rn AS(SELECT *, Row_Number() OVER(PARTITION BY BID ORDER BY XER) Rn FROM Jic j)
SELECT j1.BID, Cast(Rn1.XER AS varchar(5))+','+Cast(Rn2.XER AS varchar(5))+','+Cast(rn3.XER AS varchar(5)) XERs
FROM Jic j1
JOIN Rn Rn1 ON Rn1.BID=j1.BID
JOIN Rn Rn2 ON Rn2.BID = Rn1.BID
JOIN Rn Rn3 ON Rn3.BID = j1.BID
WHERE Rn1.Rn=1
AND Rn2.Rn=2
AND Rn3.Rn=3
GROUP BY J1.BID, Cast(Rn1.XER AS varchar(5))+','+Cast(Rn2.XER AS varchar(5))+','+Cast(rn3.XER AS varchar(5)) ;
-
Joe,
That kind of solution is dependent on the data always being structured with exactly the same number of elements per unique category. While it works for what was posted, folks often post hoping for a generalized methodology by abstracting what they're doing into a general case and then don't necessarily indicate that such is what they're doing. FYI...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - Joe Torre - Friday, June 30, 2017 11:10 AM
WITH Jic
AS
(
SELECT BID = 'EE', XER = 1 UNION
SELECT 'EE', 2 UNION
SELECT 'EE', 3 UNION
SELECT 'FF', 4 UNION
SELECT 'FF', 5 UNION
SELECT 'FF', 6),
Rn AS(SELECT *, Row_Number() OVER(PARTITION BY BID ORDER BY XER) Rn FROM Jic j)
SELECT j1.BID, Cast(Rn1.XER AS varchar(5))+','+Cast(Rn2.XER AS varchar(5))+','+Cast(rn3.XER AS varchar(5)) XERs
FROM Jic j1
JOIN Rn Rn1 ON Rn1.BID=j1.BID
JOIN Rn Rn2 ON Rn2.BID = Rn1.BID
JOIN Rn Rn3 ON Rn3.BID = j1.BID
WHERE Rn1.Rn=1
AND Rn2.Rn=2
AND Rn3.Rn=3
GROUP BY J1.BID, Cast(Rn1.XER AS varchar(5))+','+Cast(Rn2.XER AS varchar(5))+','+Cast(rn3.XER AS varchar(5)) ;Heh... try that method with a couple of thousand different values for BID. :0
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Neil has, in my opinion, the best approach to the problem. Here's an article by Wayne Sheffield on the technique that explains it well: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply