CODEWITHSUNDEEP
sqlsql-serverunpivot
tanay
tanay

Reputation: 27

how to Normalize the sql table in two tables or one detailed table

I would like little help with my SQL normalization. I have a table with as follows:

tbl_code

Ucode, desc, code1, code2, code3
1      aa    1      1      1
2      bb    1      2      2
3      cc    1      1      1

Now I want to make this table to normalized as: 

ucode, desc, code,  value
1      aa    code1  1     
2      bb    code1  1    
3      cc    code1  1
1      aa    code2  1

and so on...

How can I do this in SQL? Can someone please help me out?

Upvotes: 1

Views: 557

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82010

Yet another option

Select A.UCode
      ,A.[Desc]
      ,B.*
 From tbl_code A
 Cross Apply (
               values ('code1',code1)
                     ,('code2',code2)
                     ,('code3',code3)
             ) B(code,value)

Updated - Dynamic without using Dynamic SQL

Select A.Ucode
      ,A.[desc]
      ,C.*
 From  tbl_code A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Code  = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('Ucode','desc')
                 -- {or you can use} Where a.value('local-name(.)','varchar(100)') like 'code%'
             ) C

Upvotes: 1

Aura
Aura

Reputation: 1307

You can use UNPIVOT like this:

SELECT distinct ucode, desc, code, value
FROM 
(
SELECT *
FROM tbl_code
) AS cp
UNPIVOT (value
FOR code IN (code1, code2, code3)) AS up
Order by code;

And the output will look like this:

enter image description here

Upvotes: 0

Richard Hansell
Richard Hansell

Reputation: 5403

This should get you going, but you will probably end up with NULLs etc. to deal with in real life?

SELECT
    ucode,
    [desc],
    'code1' AS code,
    code1 AS [value]
FROM
    tbl_code
UNION ALL
SELECT
    ucode,
    [desc],
    'code2' AS code,
    code2 AS [value]
FROM
    tbl_code
UNION ALL
SELECT
    ucode,
    [desc],
    'code3' AS code,
    code3 AS [value]
FROM
    tbl_code;

Upvotes: 0

Related Questions