Solved

Pivot Table/Cross Table Query in SQL Server 2005

Posted on 2007-04-09
2
3,326 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

12 Experts available now in Live!

Get 1:1 Help Now