May 8, 2019 at 3:27 pm
This is working, thank you to Drew Allen from this forum. I would like to know if this can be tweaked a little.
select ai.*, pe.program_name, ac.full_name
from all_clients_view ac
left outer join alt_id_view ai
on ac.people_id = ai.people_id
left outer join program_enrollment_view pe
ac.people_id = pe.people_id
Currently, the result will be like this for a people_id who has more than one alt_id and more than one program:
Single, Mir AWARDS ID 1123 8/21/2017 Union FIRST
Single, Mir AWARDS ID 1123 8/21/2017 Parenting
Single, Mir NJ SPIRIT ID / DCPP Family ID 159 8/21/2017 Union FIRST
Single, Mir NJ SPIRIT ID / DCPP Family ID 159 8 8/21/2017 Parenting
Could the result be like this:
Single, Mir, AWARDS ID 1123 NJ SPIRIT ID/ DCPP Family ID 159 08/21/2017 Union FIRST Parenting
May 8, 2019 at 6:44 pm
There isn't a lot of info here and it seems like someone was already helping you on a previous thread. However, if you are just looking for a method to "combine" rows here is an easy one.
DECLARE @test TABLE (ID INT, SomeColumn VARCHAR(20))
INSERT INTO @test (ID, SomeColumn)
VALUES (1, 'First'), (2, 'Second'), (3, 'Third')
DECLARE @List VARCHAR(1000)
---Seperated by a space
SELECT @List = ISNULL(@List,'') + SomeColumn + ' ' FROM @test ORDER BY ID
SELECT @List AS Example1
--or delimited
SET @List = ''
--using a comma here but it could be anything
SELECT @List = ISNULL(@List,'') + SomeColumn + ', ' FROM @test ORDER BY ID
--this part is to strip out the unwanted character at the end
SET @List = SUBSTRING(@List, 1, LEN(@List)-1)
SELECT @List AS Example2
May 8, 2019 at 7:54 pm
There isn't a lot of info here and it seems like someone was already helping you on a previous thread. However, if you are just looking for a method to "combine" rows here is an easy one.
DECLARE @test TABLE (ID INT, SomeColumn VARCHAR(20))
INSERT INTO @test (ID, SomeColumn)
VALUES (1, 'First'), (2, 'Second'), (3, 'Third')
DECLARE @List VARCHAR(1000)
---Seperated by a space
SELECT @List = ISNULL(@List,'') + SomeColumn + ' ' FROM @test ORDER BY ID
SELECT @List AS Example1
--or delimited
SET @List = ''
--using a comma here but it could be anything
SELECT @List = ISNULL(@List,'') + SomeColumn + ', ' FROM @test ORDER BY ID
--this part is to strip out the unwanted character at the end
SET @List = SUBSTRING(@List, 1, LEN(@List)-1)
SELECT @List AS Example2
This method of concatenating strings is undocumented and unsupported. You are better off using STRING_AGG() if you are on SQL 2017 (or later) and XML concatenation otherwise.
I also wanted to note that it is MUCH, MUCH simpler to have the extra character at the beginning rather than the end of the string. You know that the beginning of the string will always be 1, so you don't need to test to find out where it is. So, instead of doing SomeColumn + ',' use ',' + SomeColumn. I also find it easier to use STUFF to replace the first character with an empty string.
Finally, it's not clear whether the OP wants a single string or multiple instances of each field. In the last case, a CROSSTAB would be a better choice. (PIVOT is a bad choice here, because you can only pivot one column at a time, and you want to pivot three columns.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 9, 2019 at 12:58 pm
This was removed by the editor as SPAM
May 9, 2019 at 1:29 pm
This method of concatenating strings is undocumented and unsupported. You are better off using
STRING_AGG()if you are on SQL 2017 (or later) and XML concatenation otherwise.
I only have a couple of 2017 instances in my environment but I've never used STRING_AGG(). Thanks for mentioning that, I'll have to check that out. I initially contemplated also showing the XML method but it's definitely a little tougher to work with.
Here is a third example using XML (formatted to break it down easier):
DECLARE @test TABLE (ID INT, SomeColumn VARCHAR(20))
INSERT INTO @test (ID, SomeColumn)
VALUES (1, 'First'), (2, 'Second'), (3, 'Third')
SELECT
STUFF (
(
SELECT
', ' + SomeColumn
FROM @test
ORDER BY ID
FOR XML PATH (''), TYPE
).value('.', 'VARCHAR(MAX)')
, 1, 2, '') AS Example3
As for possibly using a crosstab, I think I'll defer and see if the OP will respond with more context and sample data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply