Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Interesting Question in SQL Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 4:58 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:02 PM
Points: 90, Visits: 400
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'

Post #1423549
Posted Monday, February 25, 2013 5:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1423553
Posted Monday, February 25, 2013 11:32 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:02 PM
Points: 90, Visits: 400
Terrific....Exactly what I needed and such a prompt response.
Super Thanks
Post #1423889
Posted Tuesday, February 26, 2013 12:47 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:02 PM
Points: 90, Visits: 400
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.
Post #1423909
Posted Tuesday, February 26, 2013 1:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
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/
Post #1423915
Posted Tuesday, February 26, 2013 2:53 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:02 PM
Points: 90, Visits: 400
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.


  Post Attachments 
Test Data.jpg (11 views, 107.51 KB)
Post #1423953
Posted Tuesday, February 26, 2013 2:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
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/
Post #1423956
Posted Tuesday, February 26, 2013 3:52 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:02 PM
Points: 90, Visits: 400
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.....
Post #1423982
Posted Wednesday, February 27, 2013 12:52 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:02 PM
Points: 90, Visits: 400
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.
Post #1424710
Posted Wednesday, February 27, 2013 9:19 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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 ):

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;

Post #1424879
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse