Solved

Pivot Table/Cross Table Query in SQL Server 2005

Posted on 2007-04-09
2
3,332 Views
Last Modified: 2008-01-09
I have the following sql statement:
SELECT     nvarchar10, nvarchar11, nvarchar12, nvarchar13, nvarchar14, nvarchar15, nvarchar16, nvarchar17, nvarchar18, nvarchar19, nvarchar20, nvarchar21, nvarchar22, nvarchar23, nvarchar24, nvarchar25, nvarchar26, nvarchar27, nvarchar28, nvarchar29, nvarchar30
FROM         UserData  WHERE     (tp_ListId = 'c762e429-2c60-4ffd-bfe1-c90254ba294c') AND (CAST(nvarchar10 AS int) >= CAST(1 AS int)) AND (CAST(nvarchar10 AS int) <= CAST(13 AS int))

It produces:
0001      EOG      DLE      ACS      NULL      NULL      NULL      NULL      NULL      NULL      NULL      EEIC      SSC      ENRC      NULL      NULL      NULL      NULL      NULL      NULL      NULL

0003      SBA      DMS      RET      DBF      FSA      FAC      FLC      PBA      ACS      EOG      GEAC      GEAC      GEAC      GEAC      SSC      SSC      SSC      SSC      NULL      NULL

I need to have columns nvarchar11-30 grouped , then sorted with the column nvarchar10 displayed under each one. For example:

ACS
0001
0003

DLE
0001
0003

DMS
0001

FAC
0003





0
Comment
Question by:reecyp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 18878919
SELECT DISTINCT z.Col2, z.Col1
FROM (
SELECT nvarchar10 AS Col1, nvarchar11 AS Col2
FROM UserData
WHERE tp_ListId = 'c762e429-2c60-4ffd-bfe1-c90254ba294c' AND
    (CAST(nvarchar10 AS int) BETWEEN 1 AND 13)
UNION
SELECT nvarchar10 AS Col1, nvarchar12 AS Col2
FROM UserData
WHERE tp_ListId = 'c762e429-2c60-4ffd-bfe1-c90254ba294c' AND
    (CAST(nvarchar10 AS int) BETWEEN 1 AND 13)
UNION
SELECT nvarchar10 AS Col1, nvarchar13 AS Col2
FROM UserData
WHERE tp_ListId = 'c762e429-2c60-4ffd-bfe1-c90254ba294c' AND
    (CAST(nvarchar10 AS int) BETWEEN 1 AND 13)
UNION
SELECT nvarchar10 AS Col1, nvarchar14 AS Col2
FROM UserData
WHERE tp_ListId = 'c762e429-2c60-4ffd-bfe1-c90254ba294c' AND
    (CAST(nvarchar10 AS int) BETWEEN 1 AND 13)
UNION
SELECT nvarchar10 AS Col1, nvarchar15 AS Col2
FROM UserData
WHERE tp_ListId = 'c762e429-2c60-4ffd-bfe1-c90254ba294c' AND
    (CAST(nvarchar10 AS int) BETWEEN 1 AND 13)
UNION
< you get the idea; fill in as needed >
SELECT nvarchar10 AS Col1, nvarchar30 AS Col2
FROM UserData
WHERE tp_ListId = 'c762e429-2c60-4ffd-bfe1-c90254ba294c' AND
    (CAST(nvarchar10 AS int) BETWEEN 1 AND 13)
) z
ORDER BY z.Col2, z.Col1
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 250 total points
ID: 18879241
Don't know if I have all the typos flushed out, but if you can do that part, this should produce the result you want.


WITH myList(tp_ListID, MyNum, MyCode) AS
(
          SELECT tp_ListID, nvarchar10, nvarchar11 from userdata
    UNION SELECT tp_ListID, nvarchar10, nvarchar12 from userdata
    UNION SELECT tp_ListID, nvarchar10, nvarchar13 from userdata
    ...
    UNION SELECT tp_ListID, nvarchar10,nvarchar30 from userdata
)

SELECT myAns FROM
(
SELECT '' AS MyCode, '' myAns, '' AS MySort, '' AS tp_LISTID WHERE 1=2
UNION SELECT Distinct MyCode, MyCode, '',  tp_ListID from myList
WHERE tp_ListId = 'c762e429-2c60-4ffd-bfe1-c90254ba294c' AND CAST(myNum AS int) BETWEEN 1 AND 13
UNION SELECT          MyCode, myNum, myNum,tp_ListID from myList
WHERE tp_ListId = 'c762e429-2c60-4ffd-bfe1-c90254ba294c' AND CAST(myNum AS int) BETWEEN 1 AND 13
)

ORDER BY myCode, mySort
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

628 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