How to group if there is nothing to aggregate

Question

How to group if there is nothing to aggregate

mathewspsimon

SSCommitted

Points: 1721
More actions
May 26, 2010 at 4:57 am

#387018

I have a table like this
CREATE TABLE #OBD([User] varchar(255),[Times] datetime)
INSERT INTO #OBD VALUES ('user1','2/18/10 18:09')
INSERT INTO #OBD VALUES ('user1', '2/18/10 9:46')
INSERT INTO #OBD VALUES ('user2','2/18/10 18:13')
INSERT INTO #OBD VALUES ('user2','2/18/10 18:12')
INSERT INTO #OBD VALUES ('user2','2/18/10 18:12')
INSERT INTO #OBD VALUES ('user2','2/18/10 18:04')
INSERT INTO #OBD VALUES('user2','2/18/10 18:05')
INSERT INTO #OBD VALUES ('user2','2/18/10 18:06')
INSERT INTO #OBD VALUES ('user2','2/18/10 18:07')
INSERT INTO #OBD VALUES ('user1','2/18/10 9:39')
INSERT INTO #OBD VALUES ('user1','2/18/10 9:38')
What output I need is as given below -
User1User2
18-Feb-109:3818:04
I tried using CASE but I am not getting in this manner.Can someone help please?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Look up PIVOT. Start with the SQL Books Online (sql help file)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hi there, as Gail said, you will have to look up at PIVOT.

For starters, i have coded for your requirement, but this is only w.r.t the sample data you provided! Btw, thank you so much for the sample data and table, helped me a lot!

;WITH CTE AS

(

SELECT [User] , MIN([Times] ) MIN_TIME

FROM #OBD

GROUP BY [User]

),

CTE2 AS

(

SELECT [User] ,

CONVERT(VARCHAR,MIN_TIME, 110 ) DATE_ALONE,

CONVERT(VARCHAR,MIN_TIME, 108 ) TIME_ALONE

FROM CTE

)

SELECT * FROM

(

SELECT [User] ,

DATE_ALONE,

TIME_ALONE

FROM CTE2

) PIVOT_TABLE

PIVOT

(MAX([TIME_ALONE]) FOR [User] IN ([user1],[user2]) ) PIVOT_HANDLE

Tell us back here if this helped you! Hope it helped !;-)

mathewspsimon SSCommitted Points: 1721 More actions · Answer 3

hi cold coffee,thanks a lot.it worked.yes i am working on 2005 but dont have much idea about the newer stuff.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

mathewspsimon (5/26/2010)
yes i am working on 2005 but dont have much idea about the newer stuff.

Then look at this as an opportunity to get familiar with one of the 'newer' features.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass