Converting two column as row from a table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

hello to Alls,

i need some column of a table as row.but i am not able to achieve this.i uses unpvoit but its working only with one column.

here is my table stracture

[name] [pincode] [a1] [b1] [a2] [b2] [a3] [b3] [a4] [b4] [c] [d] [many more]

satya 2047461 10 101 11 111 12 121 13 131 50 60 anything

i want to result like this

[name] [pincode] [a] [b] [c] [d] [manymore]

satya 2047461 10 101 50 60 anything
satya 2047461 11 111 50 60 anything
satya 2047461 12 121 50 60 anything

please help this is a real time problem.
i need this.

thanks

Posted 22-Nov-11 21:58pm

S.P.Tiwari

Add a Solution

Comments

S.P.Tiwari 29-Nov-11 7:15am

hi everyone i got a solution of this question.

3 solutions

Solution 1

I suspect that the easiest and most maintainable way to do this is either:
1) Change your table structure, so that you have two tables:
dataRecord [a][b][c][d]...
userRecord [name][pincode] [data1][data2][data3][data4]
Then you can use much more understandable constructs to access your data.
2) Create a stored procedure that creates a temporary table and breaks your record into parts into it. It then returns the temporary table content.

Personally - I would go for option (1) if I could.

Posted 22-Nov-11 22:10pm

OriginalGriff

Comments

S.P.Tiwari 23-Nov-11 4:20am

thank you sir for response immidiatly.

but i can't change the table structure because its a client data into an excel sheet.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Heino Zunzer · Accepted Answer · 2011-11-22T23:00:00

to get the result you are looking for you can use a union

select name, pincode, a1, b1, c, d, manymore from tab
union all
select name, pincode, a2, b2, c, d, manymore from tab
union all
select name, pincode, a3, b3, c, d, manymore from tab

more on unions here
http://msdn.microsoft.com/en-us/library/ms180026.aspx[^]

but best case would still to change the structure of the table(s)...

S.P.Tiwari · Accepted Answer · 2011-11-29T01:13:00

SQL

//first UNPIVOT table with one column [a] for [a1] [a2] [a3]

select [name],[Pincode][a],identity(int,1,1) as Pk
    into #Temp1  from
    (select tbl1.[Pincode],tbl1.[name],tbl1.[a1],tbl1.[a2],tbl1.[a3] from tablename as tbl1) as up
UNPIVOT
    ([a] for [tempval] in ([a1],[a2],[a3])
    ) as pvt

// second UNPIVOT table with one column [b] for [b1] [b2] [b3]

select  [Pincode],[name],[b],IDENTITY(int,1,1) as pk into #Temp2
    from
    (select tbl2.[Pincode],tbl2.[name],tbl2.[b1],tbl2.[b2],tbl2.[b3] from tablename as tbl2) as up
UNPIVOT
    ([b] for[tempval] in ([b1],[b2],[b3])
    )
    as unpvt

// this will br the output of the both above table

    select  temptable1.pk,temptable1.[name],temptable1.[pincode],temptable1.a,temptable2.b,
     into #Temp3 from  #Temp1 temptable1
inner join  #Temp2 temptable2 on temptable1.pk = temptable2.pk


//the last tabll has the proper record "#Temp3"

Converting two column as row from a table

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0