将行转换为SQL Server中的列 [英] Convert rows into column in SQL server

问题描述

查询如下



SELECT A._id，B.firstname +''''+ B.lastname +''''+ B.surname

作为名称，A.details

来自dbo.tbl_farmer_farmdetails_'+ @originname +'A

INNER JOIN dbo.tbl_farmerregistration_'+ @originname +' B ON B.farmerctscode = A.farmer_id

WHERE B.farmerseason ='2018'



运行上面的代码得到如下输出



id姓名详情



1 Test1Product1：Cilo，Product2： Zilo



i想要输出如下



id名称产品1产品2



1 Test1 Cilo Zilo



来自我上面的sql查询我需要做些什么改变来获得上面的输出





i想想下面这样做



1.创建临时表。 />


2.使用循环溢出记录基于：详细列中的冒号。



3.将每条记录插入表格中



4.使用select语句从临时表中获取所有记录





如何执行上述步骤以获得我的以下输出如下



i想要输出如下



id名称产品1产品2



1 Test1 Cilo Zilo



我尝试过：



查询如下



SELECT A._id，B.firstname +''''+ B.lastname +''''+ B.surname

作为名称，A.details

来自dbo.tbl_farmer_farmdetails_'+ @originname +'A

INNER JOIN dbo.tbl_farmerregistration_'+ @originname +'B ON B.farmerctscode = A.farmer_id

WHERE B.farmerseason ='2018'



运行上面的代码得到输出如下



id名称Det ails



1 Test1Product1：Cilo，Product2：Zilo



i希望输出如下



id名称产品1产品2



1 Test1 Cilo Zilo



来自我上面的SQL查询我需要做些什么改变来获得上面的输出





i想想下面这样做



1.创建临时表。



2.使用循环溢出记录基于：冒号详细列。



3.将每条记录插入表格



4.使用select语句从临时表中获取所有记录





如何进行上述步骤获取我的下面输出如下



i想要输出如下



id名称产品1产品2



1 Test1 Cilo Zilo

解决方案

选择Firstname，Amount，PostalCode，LastName，AccountNumber 
来自
（
选择值，列名
来自yourtable 
）d 
 pivot 
（ 
 max（value）
 for columnname in（Firstname，Amount，PostalCode，LastName，AccountNumber）
）piv; 

如果你你有一个未知数量的要移调的列名，那么你可以使用动态SQL：

 DECLARE @cols AS NVARCHAR（MAX），
 @query AS NVARCHAR （MAX）
 
选择@cols = STUFF（（SELECT'，'+ QUOTENAME（ColumnName）
来自可贵的
 group by ColumnName，id 
 order by id 
 FOR XML PATH（''），TYPE 
）.value（'。'，'NVARCHAR（MAX）'）
，1,1，''）
 
从
设置@query = N'SELECT'+ @cols + N'（
选择值，ColumnName 
来自yourtable 
）x 
 pivot 
（
 max（value）
 for ColumnName in（'+ @cols + N'）
）p'
 
 exec sp_executesql @query; 

Query as follows

SELECT A._id,B.firstname + '' '' + B.lastname + '' '' + B.surname
as name,A.details
FROM dbo.tbl_farmer_farmdetails_' + @originname + ' A
INNER JOIN dbo.tbl_farmerregistration_' + @originname + ' B ON B.farmerctscode = A.farmer_id
WHERE B.farmerseason = '2018'

When run the above code get output as follows

id Name Details

1 Test1 "Product1":"Cilo","Product2":"Zilo"

i want the output as follows

id Name Product1 Product2

1 Test1 Cilo Zilo

from my above sql query what changes i have to made to get the above output


i think below way to do it

1.Create the temp table.

2. Use the loop to spilt the record based on : colon in "detail" column.

3.insert the each record into table

4.Use the select statement to fetch all records from temp table


how to do the above step to get my below output as follows

i want the output as follows

id Name Product1 Product2

1 Test1 Cilo Zilo

What I have tried:

Query as follows

SELECT A._id,B.firstname + '' '' + B.lastname + '' '' + B.surname
as name,A.details
FROM dbo.tbl_farmer_farmdetails_' + @originname + ' A
INNER JOIN dbo.tbl_farmerregistration_' + @originname + ' B ON B.farmerctscode = A.farmer_id
WHERE B.farmerseason = '2018'

When run the above code get output as follows

id Name Details

1 Test1 "Product1":"Cilo","Product2":"Zilo"

i want the output as follows

id Name Product1 Product2

1 Test1 Cilo Zilo

from my above sql query what changes i have to made to get the above output


i think below way to do it

1.Create the temp table.

2. Use the loop to spilt the record based on : colon in "detail" column.

3.insert the each record into table

4.Use the select statement to fetch all records from temp table


how to do the above step to get my below output as follows

i want the output as follows

id Name Product1 Product2

1 Test1 Cilo Zilo

解决方案

select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
  select value, columnname
  from yourtable
) d
pivot
(
  max(value)
  for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;

If you have an unknown number of columnnames that you want to transpose, then you can use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

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