如何将列名称作为具有值的行 [英] How to get Column names as rows with values

问题描述

我有以下数据

Hi,
I have some data as follows

coverage services medical
  500      1000    1500

i需要sql输出如下

i need sql output as follows

type    desc     value
 A     coverage   500
 B     services   1000
 C     medical    1500 

提前致谢

Thanks in advance

推荐答案

试试这个解决方案我在AdventureWorks2008数据库中试过它

Try with this solution I have tried it in AdventureWorks2008 Database

USE AdventureWorks2008

SELECT ColName,data
FROM HumanResources.vEmployee
UNPIVOT
(
	Data
	For ColName IN ([FirstName],[EmailAddress])
)Unpiv

Create Table #Temp
		(
		coverage Real,
		services Real,
		medical Real
		)

Insert into #Temp 
Values(500,1000,1500)

Select Char(64+Row_Number() Over(Order by value)) [type],[desc],[value] 
From 
   (Select coverage,services,medical From #Temp)p
UNPIVOT 
   (value for [desc] in (coverage,services,medical)) unpvt

-- Output:
--type	desc	   value
-----	-------    ------
--A	coverage   500
--B	services   1000
--C	medical	   1500

Drop Table #Temp 

这是一个显示如何转换的示例一个数据表的列到另一个数据表



这里的数据表列dtm是转换后的行数dt

this is an example to show how to convert columns of one data table to another data table

here columns of data table dtm is converted rows of dt

for (int i = 0; i < dtm.Columns.Count; i++)
                    {
                        DataRow newRow = dt.NewRow();
                        newRow[0] = dtm.Columns[i].Caption;
                        for (int j = 0; j < dtm.Rows.Count; j++)
                        {
                            if (dtm.Rows[j][i].ToString() != "")
                                newRow[j + 1] = dtm.Rows[j][i].ToString();
                        }
                        dt.Rows.Add(newRow);
                    }

i建议你不要搜索sql查询来转换你的所有数据数据表然后转换它

i suggest u instead of searching sql query for converting take all your data in data table then convert it

这篇关于如何将列名称作为具有值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！