0

I'm trying to build a report from user licenses from an application. One user can have several licenses as shown below:

user_name   license name
---------   -------------------------
toreilly    GWM Generation Moneypoint
toreilly    GWM Generation Aghada
toreilly    GWM Generation Ardnacrusha
toreilly    GWM Asset Development
toreilly    GWM Generation Erne
toreilly    GWM Generation Dublin Bay Power
toreilly    GWM Generation Lough Ree Power
toreilly    GWM Generation Lee

What I'm trying to do is show the output in one row only and the several license names into several columns like this:

user_name       license name license name
--------------- --------------------------  ---------------------   
toreilly    GWM Generation Moneypoint   GWM Generation Aghada


Can you help me on this?

Thanks, Bruno

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
Bruno Miguel
  • 11
  • 4
  • Wouldn't two columns be sufficient? One with the user_name and one with a comma delimited list of the license names? – Radu Gheorghiu Oct 15 '15 at 15:02
  • Look into pivot https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx – Reinard Oct 15 '15 at 15:14
  • Correct me if I'm wrong but to use PIVOT don't you need an aggregate function? I just want the output. The problem about comma delimited list is because this is going to be automated using an SSIS package that extracts the data into excel but because a user can have many licenses it truncates the result set in excel. – Bruno Miguel Oct 15 '15 at 16:05

1 Answers1

0

With MySql f.e you could simply use:

SELECT user_name group_concat(license) FROM licenses GROUP BY user_name

This would return rows with the username and a coma separated list of licenses.

MSQL doesn't have the group_concat function, so you'd need to use something like this:

SELECT user_name, licenses=STUFF(
  (SELECT ','+licence FROM licenses WHERE user_name=X.user_name FOR XMLPATH('')) , 1 , 1 , '' )
FROM licenses X
GROUP BY user_name

untested and orientated on this

edit: This isn't exactly what you described, as the licenses are now in one column, but the nearest i could think of.

Community
  • 1
  • 1
SleepProgger
  • 364
  • 6
  • 20
  • The problem is I'm avoiding comma separated list because this is going to be automated using an SSIS package that extracts the data into excel but because a user can have many licenses it truncates the result set in excel. – Bruno Miguel Oct 15 '15 at 16:03