Solved

SQLserver 9 Cross tab query

Posted on 2014-12-10
1
54 Views
Last Modified: 2015-02-26
Greeting,
I have a select stmt(select NUM,Week_Day,InOUT from TBL1) which give me the data in the following format.
NUM     Week_Day     InOut
0001       MONDAY     8-16
0001       TUESDAY     8-15
0001       WEDNESDAY 9-4
0002       FRIDAY    9-14
I  need to have a query which give me the following output
NUM    MONDAY   TUESDAY  WEDNESDAY  THURSDAY   FRIDAY
0001     8-16             8-15           9-4
0002                                                                                     9-14
0
Comment
Question by:mrong
1 Comment
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40492635
You need to use PIVOT for this:

SELECT NUM, [MONDAY], [TUESDAY], [WEDNESDAY], [THURSDAY], [FRIDAY]
FROM 
(SELECT NUM,Week_Day,InOUT from TBL1) p
PIVOT
(
MAX(InOUT)
FOR Week_Day IN
([MONDAY], [TUESDAY], [WEDNESDAY], [THURSDAY], [FRIDAY])
) AS pvt
ORDER BY NUM

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now