Rows to columns SQL query

Question

I am writing a query that will be used in a .NET application, therefore I would like the SQL Server 2008 o do much of the processing for me instead of the client PC that the application will run on.

I am trying to get data from some tables I joined together.

Here is an example:

SELECT SUBSTRING(CAST(CAST(HCPD.SeferCikisZamani AS TIME) AS VARCHAR), 1, 5) AS Kalkis, SUBSTRING(CAST(CAST(HCPD.DonusZamani AS TIME) AS VARCHAR), 1, 5) AS Donus, V.AracPlaka
FROM HAT_CALISMA_PLANI HCP WITH(NOLOCK)
INNER JOIN HAT_CALISMA_PLANI_DETAY HCPD WITH(NOLOCK) ON HCPD.HatCalismaPlaniKey = HCP.HatCalismaPlaniKey
INNER JOIN VALIDATOR V WITH(NOLOCK) ON V.ValidatorKey = HCPD.ValidatorKey
WHERE HCP.HatKey = 2 AND CAST(HCPD.SeferCikisZamani AS DATE) = '2012-09-25'
ORDER BY Kalkis

This query returns this result:

Kalkis  Donus   AracPlaka
-------------------------
01:51   02:01   07 ABY 04
02:02   02:12   07 AB 978
02:21   02:31   07 ABY 04
02:32   02:42   07 AB 978
03:01   03:11   07 ABY 04
03:02   03:12   07 AB 978
03:31   03:41   07 ABY 04
03:42   03:52   07 AB 978
04:01   04:11   07 ABY 04

However, In my report they are supposed to look like this,

        07 ABY 04   07 AB 978
Kalkis  06:15       06:30
Donus   07:45       08:00
Kalkis  08:00       08:10
Donus   09:30       09:40
Kalkis  10:00       10:15
Donus   11:30       11:45
Kalkis  12:30       12:45
Donus   14:00       14:15

By the way, these are like bus schedules. Anyone know a good way to achieve this?

Using pivot function and get your result. Use this link msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Vikram Jain
– Vikram Jain, Commented Sep 27, 2012 at 10:15
What is the relations between the times in the pivoted table and the times in the original one? for example in the first row Kalkis 06:15 06:30 where does 06:15 and 06:3 come from?? — Mahmoud Gamal
– Mahmoud Gamal, Commented Sep 27, 2012 at 10:24

Taryn · Accepted Answer · 2012-09-29 14:01:17Z

4

It would be easier to see the data before you queried it, but if you want to use your existing query, you can do the following:

select *
from
(
  select AracPlaka, val, col,
     row_number() over(partition by AracPlaka order by VAL, col) rn
  from
  (
    SELECT SUBSTRING(CAST(CAST(HCPD.SeferCikisZamani AS TIME) AS VARCHAR), 1, 5) AS Kalkis, 
      SUBSTRING(CAST(CAST(HCPD.DonusZamani AS TIME) AS VARCHAR), 1, 5) AS Donus, 
      V.AracPlaka
    FROM HAT_CALISMA_PLANI HCP WITH(NOLOCK)
    INNER JOIN HAT_CALISMA_PLANI_DETAY HCPD WITH(NOLOCK) 
      ON HCPD.HatCalismaPlaniKey = HCP.HatCalismaPlaniKey
    INNER JOIN VALIDATOR V WITH(NOLOCK) 
      ON V.ValidatorKey = HCPD.ValidatorKey
    WHERE HCP.HatKey = 2 
      AND CAST(HCPD.SeferCikisZamani AS DATE) = '2012-09-25'
   -- ORDER BY Kalkis
  ) x
  unpivot
  (
    val
    for col in(Kalkis, Donus)
  ) u
) x1
pivot
(
  max(val)
  for AracPlaka In([07 ABY 04], [07 AB 978])
) p

Since you are attempting to PIVOT two columns, the easiest way is to UNPIVOT the Kalkis and the Donus columns first, then apply the PIVOT to the data.

Here is a SQL Fiddle with Demo (excludes your original query)

edited Sep 29, 2012 at 14:01

answered Sep 27, 2012 at 10:29

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Cute Bear Over a year ago

thank you for your brilliant answer. but it only returns one Donus and one Kalkis. It was supposed to go like Donus, Kalkis, Donus, Kalkis, Donus, Kalkis... col 07 ABY 04 07 AB 978 Donus 04:11 03:52 Kalkis 04:01 03:42

Taryn Over a year ago

@MehmetBudak can you create a sql fiddle with a working data model from each table? Then I can review the query and try to get it working.

Cute Bear Over a year ago

you are brilliant my friend! you are just brilliant! thank you for your time!

Cute Bear Over a year ago

one last question @bluefeet, how can I fill this part dynamically? max(val) for AracPlaka In([07 ABY 04], [07 AB 978]). This information is in this table VALIDATOR (AracPlaka)

Cute Bear Over a year ago

and also I am able to get appropriate AracPlaka information from this query.

SELECT V.AracPlaka     FROM HAT_CALISMA_PLANI HCP WITH(NOLOCK)     INNER JOIN HAT_CALISMA_PLANI_DETAY HCPD WITH(NOLOCK) ON HCPD.HatCalismaPlaniKey = HCP.HatCalismaPlaniKey     INNER JOIN VALIDATOR V WITH(NOLOCK) ON V.ValidatorKey = HCPD.ValidatorKey     WHERE HCP.HatKey = 2 AND CAST(HCPD.SeferCikisZamani AS DATE) = '2012-09-25'     GROUP BY V.AracPlaka

|

Collectives™ on Stack Overflow

Rows to columns SQL query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related