| Author |
 Topic  |
|
jchoudja
Starting Member
USA
41 Posts |
 Posted - 02/13/2013 : 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 |
Edited by - jchoudja on 02/13/2013 11:10:05
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/13/2013 : 11:25:03
|
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/
|
 |
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/13/2013 : 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 |
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/13/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/13/2013 : 11:46:28
|
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/
|
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/14/2013 : 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 |
Edited by - jchoudja on 02/14/2013 08:44:44 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/14/2013 : 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/
|
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/14/2013 : 10:14:52
|
Thank You. What does ms and m represent? Is ms used like dbo.AgreementID?
jc |
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/14/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/14/2013 : 10:19:13
|
ms m are aliases ie short names for table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/14/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/14/2013 : 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/
|
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/14/2013 : 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 |
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/14/2013 : 12:45:52
|
There are 2 tables between Member and Agreement.
jc |
Edited by - jchoudja on 02/14/2013 12:55:02 |
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/14/2013 : 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 |
Edited by - jchoudja on 02/14/2013 12:57:17 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/15/2013 : 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 |
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/15/2013 : 09:31:41
|
Any help please?
jc |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/15/2013 : 14:12:08
|
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/
|
|
|
|
jchoudja
Starting Member
USA
41 Posts |
Posted - 02/15/2013 : 15:11:07
|
Thanks a lot for your help and disponibility. I will try that when I get home. Thanks again
jc |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/16/2013 : 02:29:06
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Topic |
|
display multiple values of a column in one row
