Aligning 2 rows into 1 row

Question

Aligning 2 rows into 1 row

Suth

SSCommitted

Points: 1559
More actions
November 13, 2015 at 9:29 am

#322306

Hi ,
I have the following query (im new to SQL)
select min(a_Session.created) loginDateTime, max(a_Session.updated) logoutDateTime, upper(appName) appName, convert(varchar(20),a_Session.created,111) createdDate, upper(userName) userName,u_role.id
from a_Session with (nolock)
inner join u_Contactrole on a_session.employeeid = u_contactrole.empid
inner join u_role on u_contactrole.roleid = u_role.id
where a_Session.created < getdate()
and appName is not null
and userName is not null
and userName <> 'system administrator'
group by upper(appName), convert(varchar(20),a_Session.created,111), upper(username), u_role.id
order by upper(appName) asc, convert(varchar(20),a_Session.created,111) desc, upper(username) desc
It returns return the following
loginDateTime logoutDateTime appNamecreatedDateuserNameid
2015-11-13 09:48:10.4232015-11-13 09:48:10.423 LOGIN/?RETURNURL=/CRM2015/11/13SANDRA SHANKS21
2015-11-13 09:48:10.4232015-11-13 09:48:10.423 LOGIN/?RETURNURL=/CRM2015/11/13SANDRA SHANKS29
My aim is to return the following
loginDateTime logoutDateTime appNamecreatedDateuserNameid
2015-11-13 09:48:10.4232015-11-13 09:48:10.423 LOGIN/?RETURNURL=/CRM2015/11/13SANDRA SHANKS21,28
Any idea on how I can go about doing this ?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

There's a standard method for this: FOR XML PATH string concatenation. But first, can you run through this derivative of your query and replace the question marks with the correct table aliases?

SELECT

MIN(s.created) loginDateTime,

MAX(s.updated) logoutDateTime,

UPPER(?.appName) appName,

CAST(s.created AS DATE) createdDate,

UPPER(?.userName) userName,

r.id

FROM a_Session s

--------------------------------

INNER JOIN u_Contactrole c

ON s.employeeid = c.empid

INNER JOIN u_role r

ON c.roleid = r.id

--------------------------------

WHERE s.created < GETDATE()

AND ?.appName IS NOT NULL

AND ?.userName IS NOT NULL

AND ?.userName <> 'system administrator'

GROUP BY

UPPER(?.appName),

CAST(s.created AS DATE),

UPPER(?.username),

r.id

ORDER BY

UPPER(?.appName) ASC,

CAST(s.created AS DATE) DESC,

UPPER(?.username) DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Suth SSCommitted Points: 1559 More actions · Answer 2

SELECT

MIN(s.created) loginDateTime,

MAX(s.updated) logoutDateTime,

UPPER(s.appName) appName,

CAST(s.created AS DATE) createdDate,

UPPER(s.userName) userName,

r.id

FROM a_Session s

--------------------------------

INNER JOIN u_Contactrole c ON s.employeeid = c.empid

INNER JOIN u_role r ON c.roleid = r.id

--------------------------------

WHERE s.created < GETDATE()

AND s.appName IS NOT NULL

AND s.userName IS NOT NULL

AND s.userName <> 'system administrator'

GROUP BY

UPPER(s.appName),

CAST(s.created AS DATE),

UPPER(s.username),

r.id

ORDER BY

UPPER(s.appName) ASC,

CAST(s.created AS DATE) DESC,

UPPER(s.username) DESC

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Try this. It won't be far off:

SELECT

MIN(s.created) loginDateTime,

MAX(s.updated) logoutDateTime,

UPPER(s.appName) appName,

CAST(s.created AS DATE) createdDate,

UPPER(s.userName) userName,

x.IDlist

FROM a_Session s

CROSS APPLY (

SELECT IDlist = STUFF(

(SELECT ',' + r.id AS [text()]

FROM u_Contactrole c

INNER JOIN u_role r

ON c.roleid = r.id

WHERE s.employeeid = c.empid

ORDER BY r.id

FOR XML PATH(''))

, 1, 1, '' )

) x

WHERE s.created < GETDATE()

AND s.appName IS NOT NULL

AND s.userName IS NOT NULL

AND s.userName <> 'system administrator'

GROUP BY

UPPER(s.appName),

CAST(s.created AS DATE),

UPPER(s.username),

x.IDlist

ORDER BY

UPPER(s.appName) ASC,

CAST(s.created AS DATE) DESC,

UPPER(s.username) DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Suth SSCommitted Points: 1559 More actions · Answer 4

Getting a error

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ',' to data type int.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

Suth (11/13/2015)
Getting a error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ',' to data type int.

In the APPLY block, cast r.id to varchar(n), where n is sufficient to cover values of id e.g. 2 or 3.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]