Solved

Pivot Table/Cross Table Query in SQL Server 2005

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

803 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