CONCAT

[gjmac2]

Hello, I need help in grouping together values in a field. Here is my query:

SELECT Admin.OrganizationToType.OrganizationId, List.OrganizationType.FieldValue
FROM Admin.OrganizationToType INNER JOIN
List.OrganizationType ON Admin.OrganizationToType.OrganizationTypeId = List.OrganizationType.OrganizationTypeId
GROUP BY Admin.OrganizationToType.OrganizationId, List.OrganizationType.FieldValue
ORDER BY Admin.OrganizationToType.OrganizationId


The filed I need to CONCAT is List.OrganizationType.FieldValue

So far, no luck. Any help would be appreciated.

 

[simian336]

CONCAT means to combine... What whould you like to combine it with.

Also a data example (and expected result) and data type might be helpful.

Simi

 

[gjmac2]

Here is sample data.

Organizationid Fieldvalue

f15e61d8-10d8-49a2-a56f-005697d5cd4f Hospital
f15e61d8-10d8-49a2-a56f-005697d5cd4f Southeast
f15e61d8-10d8-49a2-a56f-005697d5cd4f Transplant Center


I would like to get group the organizationid column into single rows for each unique organization id, and combine the values in the Fieldvalue column to look like this:

Organizationid Fieldvalue

f15e61d8-10d8-49a2-a56f-005697d5cd4f Hospital;Southeast;Transplant Center

I thought CONCAT would be the way to go, but am having a diffucult time woring this out. Any help would be appreciated.

 

[simian336]

This is the technique that I use most often. Often refered to as the FOR XML PATH method.


SELECT DISTINCT s1.[Organizationid],
STUFF(
(SELECT ', ' + s2.[Fieldvalue]
FROM yourtable AS s2
WHERE s2.[Organizationid] = s1.[Organizationid]
FOR XML PATH(''))
, 1, 1, '') AS Fieldvalue2
FROM yourtable AS s1
ORDER BY s1.[Organizationid]


Sim1

 

[gjmac2]

Thank you for your assistance, but this does not produce the results I am looking for. Thank you anyway.

 

[SQLBill]

gjmac2,

Can you give us a sample of what the query did return for you and what you expected it to look like? That way the query can be refined if necessary.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[r937]

I would like to get group the organizationid column into single rows for each unique organization id, and combine the values

please check da FAQs

faq183-2146
faq183-6466
faq183-6485

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gjmac2]

Ok, let me try to explain again. Forgive me in advance, as I am not very proficient with Programming.

Here is the query I am running right now:

SELECT OrganizationId, FieldValue
FROM dbRGANIZATIONTYPES
ORDER BY OrganizationId

The results are as follows:

OrganizationID FieldValue

f15e61d8-10d8-49a2-a56f-005697d5cd4f Hospital
f15e61d8-10d8-49a2-a56f-005697d5cd4f Transplant Center
f15e61d8-10d8-49a2-a56f-005697d5cd4f Southeast

The results I would like to get is as follows:

OrganizationID FieldValue

f15e61d8-10d8-49a2-a56f-005697d5cd4f Hospital; Tranplant Center; Southeast

In other words, a unique record for each OrganizationID with the respective FieldValues grouped together.

Thanks for any help that might be forthcoming.

 

[r937]

Thanks for any help that might be forthcoming.

did you check those FAQs?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[simian336]

My code was not tested so I thought I would double check it.

create table yourfile (Organizationid varchar(50),
Fieldvalue varchar(50))

Insert into yourfile values ('f15e61d8-10d8-49a2-a56f-005697d5cd4f','Hospital')
Insert into yourfile values ('f15e61d8-10d8-49a2-a56f-005697d5cd4f','Southeast')
Insert into yourfile values ('f15e61d8-10d8-49a2-a56f-005697d5cd4f','Transplant Center')

SELECT DISTINCT s1.[Organizationid],
STUFF(
(SELECT ', ' + s2.[Fieldvalue]
FROM yourfile AS s2
WHERE s2.[Organizationid] = s1.[Organizationid]
FOR XML PATH(''))
, 1, 1, '') AS Fieldvalue2
FROM yourfile AS s1
ORDER BY s1.[Organizationid]

Result....

f15e61d8-10d8-49a2-a56f-005697d5cd4f Hospital, Southeast, Transplant Center

What else where you looking for.

Simi