Representing rows as columns

[Rajeev]

Hi
I have a table in SQL Server 2000 that has following data:

PunchTime PunchType
11:45:00 In
12:45:00 Out
1:45:00 In
3:15:00 Out

Is there a way in SQL to represent this in the following format:
In Out In Out
11:45:00 12:45:00 1:45:00 3:15:00

Thanks

[David Portas]

You specification is unclear in several respects. What is the datatype of
the PunchTime column (DATETIME or CHAR maybe)? What is the primary key of
this table? Do we know whether the times are AM or PM? Why is 1:45 shown
after 12:45 in your required result?

The best answer will be to do it in your client application. What you want
is purely presentational and presentational functionality belongs
client-side.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

[posted and mailed, please reply in news]

Rajeev (na*****@yahoo.com) writes:

I have a table in SQL Server 2000 that has following data:

PunchTime PunchType
11:45:00 In
12:45:00 Out
1:45:00 In
3:15:00 Out

Is there a way in SQL to represent this in the following format:
In Out In Out
11:45:00 12:45:00 1:45:00 3:15:00

There is no built-in construct, but there are a couple of possibilities
to depending on your requirements.

For this particular case, you could to this, under the assumption that
you have at most four rows per day:

SELECT In = in1.PunchTime, Out = out1.PunchTime,
In = in2.PunchTime, Out = out2.PunchTime
FROM tbl in1
JOIN tbl out1 ON in1.PunchDate = out1.PunchDate
LEFT JOIN tbl in2 ON in1.Punchdate = in2.PunchDate
AND in1.PunchType = in2.PunchType
AND in1.PunchType < in2.PunchType
LEFT JOIN tbl out2 ON out1.Punchdate = out2.PunchDate
AND out1.PunchType = out2.PunchType
AND out1.PunchType < out2.PunchType
WHERE in1.PunchType = 'In'
AND out1.PunchType = 'Out'
Here I have assumed there is a date column in the table, since that
would make sense. I have also been lazy and assumed that there is
always one In and one Out each day.

In a more general columns where you want dynamic column names etc,
you have to build dynamic SQL. But before you do that, check out
the third-party tool RAC, http://www.rac4sql.net/ which aspires to
be the ultimate tool for crosstab queries.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp