Interesting Question in SQL
-
Hi Everyone....
I have a table (no Primary Key defined) with some value and I need to create a view on that table to get a desired output. Sample Data for both is as below. I would really appreciate if someone can take the pain of helping me out here....Thanks a ton in advance.
TABLE (Input)
IDNameCode
1abcA
1abcB
2ghtF
3jkuG
4xyzP
4xyzQ
5rstD
DESIRED VIEW RESULT (Output)
IDNameCode
1abcA,B
2ghtF
3jkuG
4xyzP,Q
5rstD
As it should be clear from above figures and values, if column 'Code' has multiple values for same column 'ID', then they need to be shown as single record with commas. e.g. 'A,B'
-
DECLARE @t TABLE(ID INT, Name CHAR(3),Code CHAR(1))
INSERT INTO @t(ID,Name,Code)
VALUES
(1, 'abc', 'A'),
(1, 'abc', 'B'),
(2, 'ght', 'F'),
(3, 'jku', 'G'),
(4, 'xyz', 'P'),
(4, 'xyz', 'Q'),
(5, 'rst', 'D');
SELECT a.ID,
a.Name,
STUFF((SELECT ',' + b.Code AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.Name = a.Name
ORDER BY b.Code
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS Code
FROM @t a
GROUP BY a.ID,a.Name
ORDER BY a.ID,a.Name;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Terrific....Exactly what I needed and such a prompt response.
Super Thanks 😀
-
Thanks Mark.....
Though I was trying to tweak your query for below requirement of mine. Basically there are mutiple columns which can have duplicate values and those need to be concatenated as well.
Table Data
IDCol A Col B Col C
1India VP Mumbai
2Aus Consultant Sydney
2Aus VP Melbourne
3France Consultant Paris
4Swiss SC Zurich
4Swiss Consultant Geneva
4Swiss VP Lusanne
5Germany Consultant Koln
5Germany SC Berlin
Output Data
IDCol A Col B Col C
1India VP Mumbai
2Aus Consultant, VP Sydney, Melbourne
3France Consultant Paris
4Swiss SC, Consultant, VP Zurich, Geneva, Lusanne
5Germany Consultant, SC Koln, Berlin
Again.....Thanks a lot in advance.
-
You can make some changes to Mark's query as below
DECLARE @t TABLE( ID INT, ColA CHAR(20), ColB CHAR(20), ColC CHAR(20) )INSERT INTO @t(ID, ColA, ColB, ColC )
VALUES
( 1, 'India', 'VP', 'Mumbai' ),
( 2, 'Aus', 'Consultant', 'Sydney' ),
( 2, 'Aus', 'VP', 'Melbourne' ),
( 3, 'France', 'Consultant', 'Paris' ),
( 4, 'Swiss', 'SC', 'Zurich' ),
( 4, 'Swiss', 'Consultant', 'Geneva' ),
( 4, 'Swiss', 'VP', 'Lusanne' ),
( 5, 'Germany', 'Consultant', 'Koln' ),
( 5, 'Germany', 'SC', 'Berlin' );
SELECT a.ID,
a.ColA,
STUFF((SELECT ',' + b.ColB AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.ColA = a.ColA
ORDER BY b.ColB
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') + ' ' +
STUFF((SELECT ',' + b.ColC AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.ColA = a.ColA
ORDER BY b.ColC
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS Col
FROM @t a
GROUP BY a.ID,a.ColA
ORDER BY a.ID,a.ColA;
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Hi Mark,
I am truly amazed by the quick response I am getting on this forum. I must say a Big Thanks before anything else.
On the code that you have provided, it gives a good hint but not the exact solution I am looking for
My input Data has 4 columns and Output should also have 4 columns, whereas your query result gives 3 columns only. Its combining column B and column C.
I am attaching a screenshot (Test Data.jpg) which shows
1) Sample Input Data in Table
2) Expected output in the View
3) Actual result that I am getting using your query.
Though, I am trying my hands to tweak the query and hope that I would get to the result myself before anyone else, if so, I will post the correct query as well.
But if anyone else can help me out here that would be just too good.
-
This should give the result you are looking for..
DECLARE @t TABLE( ID INT, ColA CHAR(20), ColB CHAR(20), ColC CHAR(20) )INSERT INTO @t(ID, ColA, ColB, ColC )
VALUES
( 1, 'India', 'VP', 'Mumbai' ),
( 2, 'Aus', 'Consultant', 'Sydney' ),
( 2, 'Aus', 'VP', 'Melbourne' ),
( 3, 'France', 'Consultant', 'Paris' ),
( 4, 'Swiss', 'SC', 'Zurich' ),
( 4, 'Swiss', 'Consultant', 'Geneva' ),
( 4, 'Swiss', 'VP', 'Lusanne' ),
( 5, 'Germany', 'Consultant', 'Koln' ),
( 5, 'Germany', 'SC', 'Berlin' );
SELECT a.ID,
a.ColA,
STUFF((SELECT ',' + b.ColB AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.ColA = a.ColA
ORDER BY b.ColB
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') as ColB,
STUFF((SELECT ',' + b.ColC AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.ColA = a.ColA
ORDER BY b.ColC
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColC
FROM @t a
GROUP BY a.ID,a.ColA
ORDER BY a.ID,a.ColA;
In case you are wondering what the code does, have a look at the article mentioned in the link below
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Edit: Added link to an article
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
This is Crazy and I am overjoyed.
Thanks to everone who took the pain to look into my Issue and been kind enough to give their precious time. Thanks Again..... 🙂
-
Hi,
I had to come back again......
I used below code, only change in what has been suggested earlier is addition of an additional record so that I get duplicate values for 5, Germany, Berlin.
DECLARE @t TABLE( ID INT, ColA nvarCHAR(max), ColB nvarCHAR(max), ColC nVarCHAR(max) )
INSERT INTO @t(ID, ColA, ColB, ColC )
VALUES
( 1, 'India', 'VP', 'Mumbai' ),
( 2, 'Aus', 'Consultant', 'Sydney' ),
( 2, 'Aus', 'VP', 'Melbourne' ),
( 3, 'France', 'Consultant', 'Paris' ),
( 4, 'Swiss', 'SC', 'Zurich' ),
( 4, 'Swiss', 'Consultant', 'Geneva' ),
( 4, 'Swiss', 'VP', 'Lusanne' ),
( 5, 'Germany', 'Consultant', 'Koln' ),
( 5, 'Germany', 'SC', 'Berlin' ),
( 5, 'Germany', 'VP', 'Berlin' );
SELECT a.ID,
a.ColA,
STUFF((SELECT ',' + b.ColB AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.ColA = a.ColA
ORDER BY b.ColB
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColB,
STUFF((SELECT ',' + b.ColC AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.ColA = a.ColA
ORDER BY b.ColC
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColC
FROM @t a
GROUP BY a.ID,a.ColA
ORDER BY a.ID,a.ColA;
In output, I want, for last record for column C, to show Berlin Once and twice.
Any suggestions around that one ? As I would have many multiple values in my actual tables and imagine Berlin repeating 5-6 times would look weird.
Last record for column C should be
"Berlin, Koln" and not
"Berlin, Berlin, Koln"
Thanks in advance.
-
To get unique vaues you just need to group the entries for the columns (that applies to all kind of queries, not only to "FOR XML" ones :hehe: ):
SELECT a.ID,a.ColA,
STUFF((SELECT ',' + b.ColB AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.ColA = a.ColA
GROUP BY b.ColB -- << here
ORDER BY b.ColB
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColB,
STUFF((SELECT ',' + b.ColC AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.ColA = a.ColA
GROUP BY b.ColC -- << and here
ORDER BY b.ColC
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColC
FROM @t a
GROUP BY a.ID,a.ColA
ORDER BY a.ID,a.ColA;
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply