SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Interesting Question in SQL


Interesting Question in SQL

Author
Message
syedathariqbal
syedathariqbal
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 623
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)

ID Name Code
1 abc A
1 abc B
2 ght F
3 jku G
4 xyz P
4 xyz Q
5 rst D

DESIRED VIEW RESULT (Output)

ID Name Code
1 abc A,B
2 ght F
3 jku G
4 xyz P,Q
5 rst D

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'
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6519 Visits: 25572

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




syedathariqbal
syedathariqbal
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 623
Terrific....Exactly what I needed and such a prompt response.
Super Thanks :-D
syedathariqbal
syedathariqbal
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 623
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
ID Col A Col B Col C
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

Output Data
ID Col A Col B Col C
1 India VP Mumbai
2 Aus Consultant, VP Sydney, Melbourne
3 France Consultant Paris
4 Swiss SC, Consultant, VP Zurich, Geneva, Lusanne
5 Germany Consultant, SC Koln, Berlin

Again.....Thanks a lot in advance.
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6066 Visits: 5280
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 Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
syedathariqbal
syedathariqbal
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 623
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.
Attachments
Test Data.jpg (14 views, 107.00 KB)
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6066 Visits: 5280
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 Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
syedathariqbal
syedathariqbal
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 623
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..... :-)
syedathariqbal
syedathariqbal
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 623
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.
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25846 Visits: 12469
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;


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search