• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

SQLserver 9 Cross tab query

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
mrong
Asked:
mrong
1 Solution
 
chaauCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now