How can I join columns instead of rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can I join columns instead of rows?

Author

Topic

PingPing
Starting Member

1 Post

Posted - 2010-04-08 : 06:52:01

I have a table called 'q1' that looks like this:

Marker Started Server Status

CRAB 03:47:20 PC3 Started
CRAB 05:38:51 PC3 Started
CRAB 06:08:23 PC2 Started
CRAB 06:19:18 PC2 Completed

I want to query to output like this:

Marker Started Server Completed

CRAB 03:47:20 PC3 NULL
CRAB 05:38:51 PC3 NULL
CRAB 06:08:23 PC2 06:19:18

I've been trying all morning with INNER JOINs and LEFT JOINs but I can't seem to crack it.

Can anyone help please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-08 : 07:00:51

select Marker, Started, Server, case when status='Completed' then Started else null end as Completed from q1

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 07:32:49

quote:
Originally posted by madhivanan

select Marker, Started, Server, case when status='Completed' then Started else null end as Completed from q1

That's not quite what the OP is after.

PingPing, can you supply a little more data, or a more indepth description of your logic? It's not quite clear exactly what you're after. eg, what would the output of the following look like?

Marker Started Server Status

CRAB 03:47:20 PC3 Started
CRAB 05:38:51 PC3 Started
CRAB 06:09:18 PC3 Completed
CRAB 06:08:23 PC2 Started
CRAB 06:19:18 PC2 Completed

From what I can see, this could either be :

CRAB 03:47:20 PC3 06:09:18
CRAB 05:38:51 PC3 06:09:18
CRAB 06:08:23 PC2 06:19:18

or:

CRAB 03:47:20 PC3 NULL
CRAB 05:38:51 PC3 06:09:18
CRAB 06:08:23 PC2 06:19:18

There are 10 types of people in the world, those that understand binary, and those that don't.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-08 : 07:42:52

Yes madhi's query is not giving required result
but i think its first step towards solution
as i also reached upto that

and I think OP is trying to display server started and completed
if it completed means for any perticular server
he found status completed then it will take time of that status
if not then null as it is not completed yet.

but this is unclear that
if a server has status started twice and one status completed
then which time will be taken from that two status

more explanation is required

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 08:18:50

may be this:-


SELECT t.Marker,
t.Started,
t.Server,
CASE WHEN t2.Started IS NULL THEN t1.Started ELSE NULL END
FROM Table t
OUTER APPLY (SELECT TOP 1 Started
             FROM Table
             WHERE Status='Completed'
             AND Server=t.Server
             AND Marker=t.Marker
             AND Started > t.Started
             ORDER BY Started ASC)t1
OUTER APPLY (SELECT TOP 1 Started
             FROM Table
             WHERE Status='Started'
             AND Server=t.Server
             AND Marker=t.Marker
             AND Started > t.Started
             AND Started < t1.Started
             ORDER BY Started ASC)t2
WHERE t.Status='Started'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 08:21:21

quote:

Originally posted by visakh16

may be this:-


SELECT t.Marker,
t.Started,
t.Server,
CASE WHEN t2.Started IS NULL THEN t1.Started ELSE NULL END
FROM Table t
OUTER APPLY (SELECT TOP 1 Started
             FROM Table
             WHERE Status='Completed'
             AND Server=t.Server
             AND Marker=t.Marker
             AND Started > t.Started)t1
OUTER APPLY (SELECT TOP 1 Started
             FROM Table
             WHERE Status='Started'
             AND Server=t.Server
             AND Marker=t.Marker
             AND Started > t.Started
             AND Started < t1.Started)t2
WHERE t.Status='Started'

Should there be an ORDER BY clause in those sub queries?

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 08:23:58

quote:

Originally posted by DBA in the making

quote:

Originally posted by visakh16

may be this:-


SELECT t.Marker,
t.Started,
t.Server,
CASE WHEN t2.Started IS NULL THEN t1.Started ELSE NULL END
FROM Table t
OUTER APPLY (SELECT TOP 1 Started
             FROM Table
             WHERE Status='Completed'
             AND Server=t.Server
             AND Marker=t.Marker
             AND Started > t.Started)t1
OUTER APPLY (SELECT TOP 1 Started
             FROM Table
             WHERE Status='Started'
             AND Server=t.Server
             AND Marker=t.Marker
             AND Started > t.Started
             AND Started < t1.Started)t2
WHERE t.Status='Started'

Should there be an ORDER BY clause in those sub queries?

There are 10 types of people in the world, those that understand binary, and those that don't.

Sorry I missed it
Have edited my original response
Thanks for the catch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources