Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 display multiple values of a column in one row

Author  Topic 

jchoudja
Starting Member

41 Posts

Posted - 2013-02-13 : 11:05:54
Hi,
How can I select and display Multiple possible values of the same row in single row seperated by coma?
exemple:
table name:
agrement
ID | type | status
-----------------------------
1 | 1 month | active
1 | cession | active
1 | day | inactive
2 | 1 month | active
2 | day | inactive

I want to display only active membership (status) for each ID in 1 row separated by coma. Note: there is no limitation on the numeber type for single ID

ID | mebership(s)
--------------------------
1 | 1 month, cession
2 | 1 month

Thank you

jc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 11:25:03
[code]
SELECT ID,
STUFF((select ',' + type from agrement where status = 'active' and id = t.id for xml path('')),1,1,'')
FROM(SELECT DISTINCT ID FROM agrement)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-13 : 11:35:00
Thank you for your verry fast answer. But It I didn't express clearly what I wanted.
I want to show only the Membership(s) field not the ID. This is just a part of my table.
Membership(s)
1 month, cession
1 month

Can you please breack it down?


jc
Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-13 : 11:42:16
My table already contains many columns and mebership(s)is showing there in multiple rows when ID has mor than 1 type. All I want is to show all multiples Type for each ID in one row.


jc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 11:46:28
[code]
SELECT
STUFF((select ',' + type from agrement where status = 'active' and id = t.id for xml path('')),1,1,'') AS memberships
FROM(SELECT DISTINCT ID FROM agrement)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-14 : 08:32:16
Thank you.
I am Sorry but I still can do it on my onwne.
I forgot to mantion data are from many tables linked togather. Here is the actual situation.

Tables
Members (MemberID,MembershipID, MemberName)
Membership (MembershipID, AgreementID, MebershipName)
Agreement (AgrementID, AgreementName, AgreementIsActive(Yes or No))

Tables are linked with: MembershipID and AgreementID

and My SQL result should look like this:
List all AgreementName for each MemberID seperated by coma

MeberID | MemberName | Agreements
----------------------------------------
1.......|_John.......|student, cession
2.......|_Meli.......|student
3.......|_Jean.......|1month, special, cession
4.......|_Chad.......|special

Note. ignore Dot(.) this is just for table formating

Thank you


jc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-14 : 09:46:15
its still the same


SELECT m.MemberID,
m.MemberName,
STUFF((SELECT ',' + AgreementName FROM Agreement WHERE AgrementID = ms.AgreementID AND AgreementIsActive='Yes' FOR XML PATH('')),1,1,'') AS Agreements
FROM membership ms
INNER JOIN members m
ON m.MembershipID = ms.membershipID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-14 : 10:14:52
Thank You. What does ms and m represent? Is ms used like dbo.AgreementID?


jc
Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-14 : 10:18:51
in my query, I use dbo.Agreement.AgreementName to specify AgrementName in Table Agreement. Should I then say like.
STUFF((SELECT ',' + dbo.AgreementName FROM Agreement ..... or hust STUFF((SELECT ',' + AgreementName FROM Agreement

jc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-14 : 10:19:13
ms m are aliases ie short names for table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-14 : 12:00:29
Here is what I did:

....
STUFF((SELECT ',' + dbo.Agreement.AgreementName FROM dbo.Agreement WHERE dbo.Agreement.AgreementID = dbo.Membership.AgreementID FOR XML PATH('')),1,1,'') AS Agreements
....
Just to collect all agreements active and no
But I am Getting Each agreement on its own line on Agreements column
What migh be wrong here?

jc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-14 : 12:03:13
show the FROM part. without which I cant understand where you're going wrong

Also whats was the issue with my query? why did you change it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-14 : 12:44:29
I see what is going wrong.
Each AgreementID is unique so memeberID=1 will get 2 differents AgreementID for his 2 differents Agreement.
In My table, only the memberID is duplicated and should be used to group all Agreements from the same member.
How can I overcome this?
Here it is as it:

SELECT DISTINCT dbo.Member.MemberGUID, dbo.Agreement.AgreementGUID, dbo.MembershipAgreement.MembershipGUID, dbo.Membership.MemberGUID AS Expr1,STUFF((SELECT ',' + dbo.Agreement.AgreementName FROM dbo.Agreement WHERE dbo.Agreement.AgreementGUID = dbo.MembershipAgreement.AgreementGUID FOR XML PATH('')),1,1,'') AS Agreements

FROM dbo.Agreement INNER JOIN
dbo.MembershipAgreement ON dbo.Agreement.AgreementGUID = dbo.MembershipAgreement.AgreementGUID INNER JOIN
dbo.Membership ON dbo.MembershipAgreement.MembershipGUID = dbo.Membership.MembershipGUID INNER JOIN
dbo.Member ON dbo.Membership.MemberGUID = dbo.Member.MemberGUID



jc
Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-14 : 12:45:52
There are 2 tables between Member and Agreement.

jc
Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-14 : 12:52:49
Here is the relation chain between tables

Member.MemberGUID -> Membership.MemberGUID Membership.MembershipGUID -> MembershipAgreement.MembershipGUID MembershipAgreement.AgreementGUID -> Agreement.AgreementGUID

Only MemberGUID is duplicated if the member has many Mebership but MembershipGUID and AgreementGUID are unique on each row.


jc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-14 : 23:05:04
please understand that its hard to make out what exactly is problem without seeing any data

why not post some data in below format to illustrate your issue?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-15 : 07:25:38
Thank you for your consideration.
Here is sample of what I want.
I have those 4 tables.
Member
MemberGUID|MemberName
----------------------
1_________|_John_____
2_________|_Celia____
3_________|_Lucas____
4_________|_Mehli____
5_________|_Lucas____

Membership
MembershipGUID|MemberGUID
-------------------------
M1____________|_1_____
M2____________|_1____
M3____________|_2____
M4____________|_2____
M5____________|_2____
M6____________|_3____
M7____________|_4____
M8____________|_5____

MebershipAgreement
MebershipAgreementGUID|MembershipGUID|AgreementGUID
---------------------------------------------------
MS1___________________|_M1___________|_A1
MS2___________________|_M2___________|_A2
MS3___________________|_M3___________|_A3
MS4___________________|_M4___________|_A4
MS5___________________|_M5___________|_A5
MS6___________________|_M6___________|_A6
MS7___________________|_M7___________|_A7
MS8___________________|_M8___________|_A8

Agreement
AgreementGUID|Agreement Name
-----------------------------
A1___________|__Mtm_____
A2___________|__cession_____
A3___________|__Mtm_____
A4___________|__single_____
A5___________|__double_____
A6___________|__single_____
A7___________|__cession_____
A8___________|__single_____

Here is the result I am looking for:

MemberGUID|MemberName|Agrements
----------------------------------------------
1_________|_John_____|_Mtm, cession
2_________|_Celia____|_Mtm, single, double
3_________|_Lucas____|_single
4_________|_Mehli____|_cession
5_________|_Lucas____|_single

I have done this:
SELECT DISTINCT dbo.Member.MemberGUID, dbo.Agreement.AgreementGUID, dbo.MembershipAgreement.MembershipGUID, dbo.Membership.MemberGUID AS Expr1,STUFF((SELECT ',' + dbo.Agreement.AgreementName FROM dbo.Agreement WHERE dbo.Agreement.AgreementGUID = dbo.MembershipAgreement.AgreementGUID FOR XML PATH('')),1,1,'') AS Agreements

FROM dbo.Agreement INNER JOIN
dbo.MembershipAgreement ON dbo.Agreement.AgreementGUID = dbo.MembershipAgreement.AgreementGUID INNER JOIN
dbo.Membership ON dbo.MembershipAgreement.MembershipGUID = dbo.Membership.MembershipGUID INNER JOIN
dbo.Member ON dbo.Membership.MemberGUID = dbo.Member.MemberGUID

But I aml getting 8 rows instate of 5 so each Agreement is showing on its own row. I want to concatenate all agreements for the same member in one row seperated by coma.
Thank you for any help.


jc
Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-15 : 09:31:41
Any help please?


jc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 14:12:08
[code]
SELECT m.*,
STUFF((SELECT ',' + a.[Agreement Name]
FROM Membership mr
JOIN MebershipAgreement ma
ON ma.MembershipGUID = mr.MembershipGUID
JOIN Agreement a
ON a.AgreementGUID = ma.AgreementGUID
WHERE MemberGUID = m.MemberGUID
ORDER BY a.AgreementGUID
FOR XML PATH('')
),1,1,'') AS Agrements
FROM Member m
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-15 : 15:11:07
Thanks a lot for your help and disponibility. I will try that when I get home. Thanks again


jc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-16 : 02:29:06
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -