将表中的两列转换为行 [英] Converting two column as row from a table

问题描述

打招呼，​​

我需要将表的某些列作为row.但是我无法实现这一点.我使用unpvoit，但它仅适用于一列.

这是我的桌子结构

[名称] [密码] [a1] [b1] [a2] [b2] [a3] [b3] [a4] [b4] [c] [d] [更多]

satya 2047461 10 101 11 111 12 121 13 131 50 60任何东西

我想要这样的结果

[姓名] [密码] [a] [b] [c] [d] [更多]

satya 2047461 10 101 50 60任何东西
satya 2047461 11 111 50 60任何东西
satya 2047461 12 121 50 60任何东西


请帮助这是一个实时问题.
我需要这个.

感谢

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

推荐答案

要获得所需的结果，可以使用联合体

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

有关工会的更多信息
http://msdn.microsoft.com/en-us/library/ms180026.aspx [ ^ ]

但最好的情况仍然是更改表的结构...

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)...

我怀疑最简单，最可维护的方法是:
1)更改表结构，以便有两个表:
dataRecord [a] [b] [c] [d] ...
userRecord [名称] [密码] [data1] [data2] [data3] [data4]
然后，您可以使用更易于理解的构造来访问数据.
2)创建一个存储过程，该存储过程创建一个临时表并将您的记录分成多个部分.然后，它返回临时表的内容.

就个人而言-如果可以的话，我会选择选项(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.

//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"

这篇关于将表中的两列转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！