Solved

Pivot Table/Cross Table Query in SQL Server 2005

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Percentage Formula 7 33
invoke-sqlcmd help 5 33
Creating Scalar Function 3 19
relocating SQL 2000 18 34
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 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