We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

UnPivot in SQL Server 2000

Medium Priority
941 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
Comment
Watch Question

Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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
Jagdish DevakuSr DB Architect

Commented:
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

Jagdish DevakuSr DB Architect

Commented:
in the above query i have added 'statusname' column in #Mytable for reference...
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.