×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

Help finding a way to show my information on one row

Help finding a way to show my information on one row

(OP)
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.

RE: Help finding a way to show my information on one row

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.

RE: Help finding a way to show my information on one row

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


RE: Help finding a way to show my information on one row

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

RE: Help finding a way to show my information on one row

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

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


---- Andy

There is a great need for a sarcasm font.

RE: Help finding a way to show my information on one row

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


RE: Help finding a way to show my information on one row

(OP)
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.

RE: Help finding a way to show my information on one row

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

RE: Help finding a way to show my information on one row

(OP)
Not a problem Duane. Did it with pleasure.

RE: Help finding a way to show my information on one row

Looks like you need to be on 2017 to use STRING_AGG

RE: Help finding a way to show my information on one row

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

RE: Help finding a way to show my information on one row

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:

CODE --> SQL

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close