Solved

UnPivot in SQL Server 2000

Posted on 2009-06-30
7
880 Views
Last Modified: 2012-05-07
Hi,

I have the following table.


-- Create Cross Tab Table


CREATE TABLE #MyCrossTable
(
      BatchId int,    
      [Status-0] int,  
      [Status-1] int,  
      [Status-2] int,  
      [Status-3] int,  
      [Status-4] int
)
GO

INSERT INTO #MyCrossTable SELECT 1001, 0, 1, 1,  0, 0
GO

INSERT INTO #MyCrossTable SELECT 1002, 1, 0, 0,  1, 1
GO

INSERT INTO #MyCrossTable SELECT 1003, 0, 0, 0,  0, 1
GO

INSERT INTO #MyCrossTable SELECT 1004, 0, 0, 0,  0, 1
GO


I need to UnPivot this Cross Tab Table  to Normal Table as following (This is the sample template)


-- Create Normal Table


CREATE TABLE #MyTable
(
      BatchId int,
      Status int
)
GO

INSERT INTO #MyTable SELECT 1001, 1
GO

INSERT INTO #MyTable SELECT 1001, 2
GO

INSERT INTO #MyTable SELECT 1002, 0
GO

INSERT INTO #MyTable SELECT 1002, 3
GO

INSERT INTO #MyTable SELECT 1002, 4
GO

INSERT INTO #MyTable SELECT 1003, 4
GO

INSERT INTO #MyTable SELECT 1004, 4
GO

Can you please provide me the T-SQL code needed for this UnPivot ?

Thanks
0
Comment
Question by:milani_lucie
  • 4
  • 2
7 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24751189
Just use UNION statement:
SELECT BatchId, 0

FROM #MyCrossTable

WHERE [Status-0] = 1

UNION

SELECT BatchId, 1

FROM #MyCrossTable

WHERE [Status-1] = 1

UNION

SELECT BatchId, 2

FROM #MyCrossTable

WHERE [Status-2] = 1

UNION

SELECT BatchId, 3

FROM #MyCrossTable

WHERE [Status-3] = 1

UNION

SELECT BatchId, 4

FROM #MyCrossTable

WHERE [Status-4] = 1

Open in new window

0
 

Author Comment

by:milani_lucie
ID: 24751210
mwvisa1:

Can you please explain this SQL in simple terms what you did ? This works really great ! Appreciate your time,  patience and great help !

Thanks
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24751249
This link my help, but I will give my own explanation as well:
http://www.w3schools.com/sql/sql_union.asp

The basic query is to take a batchid and a literal value representing the number of status if status column with the same number has a value of 1 as that indicates existence of that status in the crosstab query.

e.g.
SELECT BatchId, 4
FROM #MyCrossTable
WHERE [Status-4] = 1

This query will bring back only the batch id values that have a status 4.  

Using UNION, we can join multiple statements (one for each status possible) so that we ultimately get all the values vertically instead of horizontally.

It is late at night, but hopefully that was cohesive enough.

Best regards,
Kevin
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24752163
hi,
Please check the below code... its working...
 
please let me know if there is any issue...

CREATE TABLE #MyCrossTable

(

      BatchId int,    

      [Status-0] int,   

      [Status-1] int,   

      [Status-2] int,   

      [Status-3] int,   

      [Status-4] int

)

GO
 

CREATE TABLE #MyTable 

(

      BatchId int,

	StatusName nvarchar(20),

      Status int

)

GO
 
 

INSERT INTO #MyCrossTable SELECT 1001, 0, 1, 1,  0, 0

GO
 

INSERT INTO #MyCrossTable SELECT 1002, 1, 0, 0,  1, 1

GO
 

INSERT INTO #MyCrossTable SELECT 1003, 0, 0, 0,  0, 1

GO
 

INSERT INTO #MyCrossTable SELECT 1004, 0, 0, 0,  0, 1

GO
 
 
 

insert into #MyTable

SELECT batchid, StatusName, status

FROM (select batchid, [status-0],[status-1],[status-2],[status-3],[status-4] from #MyCrossTable)p

UNPIVOT

(status for StatusName in ( [status-0],[status-1],[status-2],[status-3],[status-4])) as unpvt

where status = 1

go
 

update #MyTable

set Status = 0

where StatusName = 'status-0'
 

update #MyTable

set Status = 1

where StatusName = 'status-1'
 

update #MyTable

set Status = 2

where StatusName = 'status-2'
 

update #MyTable

set Status = 3

where StatusName = 'status-3'
 

update #MyTable

set Status = 4

where StatusName = 'status-4'

go
 

select batchid, status from #MyTable
 

DROP table #MyCrossTable
 

DROP table #MyTable

go

Open in new window

0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24752172
in the above query i have added 'statusname' column in #Mytable for reference...
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24753804
UNPIVOT and PIVOT are SQL 2005+.  UNION is more portable and is only one statement as you can just add INSERT INTO line above it to populate the #MyTable.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24755001
If the title is incorrect and this is for SQL Server 2005, then you avoid the multiple updates by converting the UNPIVOT query to the following:
SELECT batchid, cast(right(StatusName, charindex('-', reverse(StatusName))-1) as INT) as [status]

FROM (select batchid, [status-0],[status-1],[status-2],[status-3],[status-4] from #MyCrossTable)p

UNPIVOT

(status for StatusName in ( [status-0],[status-1],[status-2],[status-3],[status-4])) as unpvt

where status = 1

Open in new window

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

20 Experts available now in Live!

Get 1:1 Help Now