CODEWITHSUNDEEP
sqlnetezza
ravindra paranjape
ravindra paranjape

Reputation: 1

How do I put Column Data to rows and assign fixed ID to them in Netezza?

My Data looks like this - 

Game      | P1  | P2    | P3    | P4    | P5  
Cricket   | Vk  | St    | Mw    | Sg    | Nm  
Football  | Cl  | Zn    |   | Un    | Rb  
Hockey    | Gd  | Sg    | Mu    |   | Hl

I want to convert them like this - 

Game    | Players   | Id  
Cricket   | Vk  | 1  
Cricket   | St  | 2  
Cricket   | Mw  | 3  
Cricket   | Sg  | 4  
Cricket   | Nm  | 5  
Football  | Cl  | 1  
Football  | Zn  | 2  
Football  | Un  | 4  
Football  | Rb  | 5  
Hockey    | Gd  | 1  
Hockey    | Sg  | 2  
Hockey    | Mu  | 3  
Hockey    | Hl  | 5

Could anybody please help me on this?

Not sure if you are able to understand this, basically it's the table i want to put but it din't allow me to put it in table manner so I have used | as breakers. For reference please view images attached

Source

Target
Thank You

Upvotes: 0

Views: 214

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

Here is another approach that doesn't use UNION ALL. The first part here is just creating some sample data to work with. This would be an ideal format for future questions as it makes it super easy for others to work on your issue.

declare @Something table
(
    Game varchar(10)
    , P1 varchar(10)
    , P2 varchar(10)
    , P3 varchar(10) 
    , P4 varchar(10) 
    , P5 varchar(10)
)

insert @Something values
('Cricket', 'Vk', 'St', 'Mw', 'Sg', 'Nm')
, ('Football', 'Cl', 'Zn', null, 'Un', 'Rb')
, ('Hockey', 'Gd', 'Sg', 'Mu', null, 'Hl')

Now using CROSS APPLY we can simulate the UNPIVOT functionality. I find this approach a lot easier to remember and understand than the UNPIVOT function which is just incredibly obtuse syntax for me.

select s.Game
    , MyValues.Name
from @Something s
cross apply (values(s.P1), (s.P2), (s.p3), (s.p4), (s.p5)) MyValues(Name)
where MyValues.Name is not null
order by s.Game
    , MyValues.Name

For completeness I put together how you could do this using UNPIVOT.

select Game
    , u.Name
from (select Game, P1, P2, P3, P4, P5  from @Something) s
unpivot (MyValues for Name in (P1, P2, P3, P4, P5)) as u

Upvotes: 0

newbie_coder
newbie_coder

Reputation: 46

I think your question has already been answered here .

Anyway, you might probably want to do a union all.

select *
into target
from (
Select Game, P1 as Players, '1' as Id
from source

union all

Select Game, P2 as Players, '2' as Id
from source

union all

Select Game, P3 as Players, '3' as Id
from source

union all

Select Game, P4 as Players, '4' as Id
from source

union all

Select Game, P5 as Players, '5' as Id
from source
)

Upvotes: 1

Related Questions