Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

UnPivot in SQL Server 2000

Posted on 2009-06-30
7
Medium Priority
?
901 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
[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
  • 4
  • 2
7 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 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 60

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 60

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 60

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 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