Pivot Table/Cross Table Query in SQL Server 2005

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





reecypAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.