SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 display multiple values of a column in one row
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

jchoudja
Starting Member

USA
41 Posts

Posted - 02/13/2013 :  11:05:54  Show Profile  Reply with Quote
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

Edited by - jchoudja on 02/13/2013 11:10:05

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/13/2013 :  11:25:03  Show Profile  Reply with Quote

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


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

Go to Top of Page

jchoudja
Starting Member

USA
41 Posts

Posted - 02/13/2013 :  11:35:00  Show Profile  Reply with Quote
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

USA
41 Posts

Posted - 02/13/2013 :  11:42:16  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/13/2013 :  11:46:28  Show Profile  Reply with Quote

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


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

Go to Top of Page

jchoudja
Starting Member

USA
41 Posts

Posted - 02/14/2013 :  08:32:16  Show Profile  Reply with Quote
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

Edited by - jchoudja on 02/14/2013 08:44:44
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/14/2013 :  09:46:15  Show Profile  Reply with Quote
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

USA
41 Posts

Posted - 02/14/2013 :  10:14:52  Show Profile  Reply with Quote
Thank You. What does ms and m represent? Is ms used like dbo.AgreementID?


jc
Go to Top of Page

jchoudja
Starting Member

USA
41 Posts

Posted - 02/14/2013 :  10:18:51  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/14/2013 :  10:19:13  Show Profile  Reply with Quote
ms m are aliases ie short names for table


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

Go to Top of Page

jchoudja
Starting Member

USA
41 Posts

Posted - 02/14/2013 :  12:00:29  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/14/2013 :  12:03:13  Show Profile  Reply with Quote
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

USA
41 Posts

Posted - 02/14/2013 :  12:44:29  Show Profile  Reply with Quote
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

USA
41 Posts

Posted - 02/14/2013 :  12:45:52  Show Profile  Reply with Quote
There are 2 tables between Member and Agreement.

jc

Edited by - jchoudja on 02/14/2013 12:55:02
Go to Top of Page

jchoudja
Starting Member

USA
41 Posts

Posted - 02/14/2013 :  12:52:49  Show Profile  Reply with Quote
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

Edited by - jchoudja on 02/14/2013 12:57:17
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/14/2013 :  23:05:04  Show Profile  Reply with Quote
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

USA
41 Posts

Posted - 02/15/2013 :  07:25:38  Show Profile  Reply with Quote
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

USA
41 Posts

Posted - 02/15/2013 :  09:31:41  Show Profile  Reply with Quote
Any help please?


jc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/15/2013 :  14:12:08  Show Profile  Reply with Quote

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


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

Go to Top of Page

jchoudja
Starting Member

USA
41 Posts

Posted - 02/15/2013 :  15:11:07  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/16/2013 :  02:29:06  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000