Help finding a way to show my information on one row 3

[EM1107]

Good day Every one.

I would like your assistance to resolve my issue.
I have a table that has 3 columns and display as it appear bellow:

ID NUMBER Title
103470114 0 Training
103470114 1 Corporate
103470114 2 Documents
103470114 3 Articles

I would like to be able to show the ID only once and get the Title column do display as one row as follow:

ID Title
103470114 Training / Corporate / Documents / Article

I have tried multiple ways of getting this done but I cannot seem to get what I want.
Would anyone know how I can accomplish this.

Your help would be greatly appreciated.

 

[Andrzejek]

In Oracle I would use LISTAGG, but in SQL Server you can use STRING_AGG
More info about it here or just Google it.


---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

maybe something along these lines?

SELECT ID, TITLE FROM Your_Table where ID in
(SELECT DISTINCT ID FROM Your_Table);

Do you have access to a Unix/Linux utilities stack in your Windows environment?
If yes, awk can "massage" your output.


==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[EM1107]

John this does not work for me It still give me this info in column and I require them on single row.

 

[Andrzejek]

Did you even try to use the STRING_AGG?
According to this article, your SQL should look something like:

[pre]
SELECT
ID,
STRING_AGG(Title,' / ')
WITHIN GROUP (ORDER BY NUMBER) NewTitle
FROM
YourTable
GROUP BY
ID
[/pre]
Just my opinion: NUMBER is not the best name for the field name...


---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

Andy has given you a good solution. You did not answer my question as to whether your output can be manipulated by a system utility before presentation to the end user. This could also be accomplished using a CURSOR that walks through the ID's and strings together the titles via concatenation before fetching the next ID. A cursor within a cursor.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[EM1107]

Sorry John I just finish testing what the solution Andy provided and it actually was the solution.
I greatly appreciate what you guys gave me as solution. This is an option I was not aware of.
Just to say we learn something new every days.

Thanks again for your input. Relay appreciated.

 

[dhookom]

Since Andy's answer worked for you consider giving him a start by clicking the Great Post! link in his answer.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[EM1107]

Not a problem Duane. Did it with pleasure.

 

[SaltyTheFrog]

Looks like you need to be on 2017 to use STRING_AGG

 

[SamBones]

Excellent solution Andy. I was about to suggest a pivot table, but this is by far a much cleaner solution. Another star for you.

 

[dhookom]

If you don't have a version of SQL Server that support STRING_AGG you can use XML PATH. I learned this here in this forum if you search a bit.

I have a table NLA_Locations of information about our factories. There is a location subgroup field that would be like your ID field. The locTitle is your Title field. My SQL that concatenates the locTitles by locSubgroup is:

SELECT  Distinct [locSubGroup],
REPLACE(STUFF((SELECT DISTINCT ' / '+ locTitle
	FROM nla_Locations L
	WHERE L.[locSubGroup] = NLA_Locations.[locSubGroup]
	FOR XML PATH('')),1,2,''),'&','&') as Locations
FROM NLA_Locations

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016