如何将行转换为列 [英] How to convert rows into columns
问题描述
我想将行转换为列。这是我的样品表。
tbl_table
FId类型
6号码
8名称
15街道
18地址
这是查询我已经解决了。
SELECT SUBSTRING((SELECT','+ CAST(FId AS VARCHAR)FROM tbl_table其中输入('地址','街道','数字','名称')FOR XML PATH('')),2,10000)AS FID
我收到了输出为
FId
6,8,15,18
它已经将行转换为列,但我的问题是,
我需要输出相同的输出我输入的内容。
我的预期输出应为
FId
18,15,6,8
请妥善解决此问题。
我的尝试:
SELECT SUBSTRING((SELECT','+ CAST(FId AS VARCHAR)来自tbl_table其中输入('地址','街道','数字','名称')FOR XML PATH('')),2,10000)AS FID
您需要在子查询中指定所需的顺序。
尝试这样的事情:
SELECT STUFF(
( SELECT ' ,' + CAST(FId As varchar ( 20 ))
FROM tbl_table
WHERE [类型] IN (' 地址',' Street',' Number',' 名称' )
ORDER BY CASE [类型]
WHEN ' 地址' 那么 0
WHEN ' Street' 那么 1
WHEN ' Number' THEN 2
ELSE 3
END
FOR XML PATH(' '), TYPE )。value(' 。',' varchar(max)')
), 1 , 1 ,' ') AS FID
STUFF
功能 [ ^ ]避免必须为子串操作提供终点。
并添加TYPE
和.value('。','varchar(max)')
确保不会有任何输出中的XML编码文本,如中所述这篇Simple Talk文章 [ ^ ]。
声明@products varchar(200)
set @ products ='地址,Street,Number,Name'
声明@individual varchar(20)
声明@count varchar(200)
set @count =''
WHILE LEN(@products)> ; 0
BEGIN
IF PATINDEX('%,%',@ product)> 0
BEGIN
SET @individual = SUBSTRING(@ product,
0,
PATINDEX('%,%',@ product))
SET @products = SUBSTRING(@ product,
LEN(@individual +',')+ 1,
LEN(@products))
select @count = @count +''+ convert( varchar,fid)来自tbl_table,其中[type] = @ individual
END
ELSE
BEGIN
SET @individual = @products
SET @products = NULL
从tbl_table中选择@count = @count +''+ convert(varchar,fid),其中[type] = @ individual
select @count
END
END
I want to convert rows into columns. here is my sample table.
tbl_table
FId Type
6 Number
8 Name
15 Street
18 Address
Here is the query I have worked out.
SELECT SUBSTRING((SELECT ',' + CAST(FId AS VARCHAR) FROM tbl_table where Type in('Address','Street','Number','Name')FOR XML PATH('')), 2,10000) AS FID
I received output as
FId
6,8,15,18
It has been converted rows into columns, But my problem is,
I need the output in same order which input i have passed inside.
My expected output should be as
FId
18,15,6,8
Kindly resolve this problem.
What I have tried:
SELECT SUBSTRING((SELECT ',' + CAST(FId AS VARCHAR) FROM tbl_table where Type in('Address','Street','Number','Name')FOR XML PATH('')), 2,10000) AS FID
You need to specify the desired order within the sub-query.
Try something like this:
SELECT STUFF( (SELECT ',' + CAST(FId As varchar(20)) FROM tbl_table WHERE [Type] IN ('Address', 'Street', 'Number', 'Name') ORDER BY CASE [Type] WHEN 'Address' THEN 0 WHEN 'Street' THEN 1 WHEN 'Number' THEN 2 ELSE 3 END FOR XML PATH(''), TYPE).value('.', 'varchar(max)') ), 1, 1, '') AS FID
TheSTUFF
function[^] avoids having to provide an end point for the substring operation.
And addingTYPE
and.value('.', 'varchar(max)')
ensures there won't be any XML-encoded text in the output, as described in this Simple Talk article[^].
Declare @products varchar(200) set @products= 'Address,Street,Number,Name' Declare @individual varchar(20) Declare @count varchar(200) set @count = '' WHILE LEN(@products) > 0 BEGIN IF PATINDEX('%,%', @products) > 0 BEGIN SET @individual = SUBSTRING(@products, 0, PATINDEX('%,%', @products)) SET @products = SUBSTRING(@products, LEN(@individual + ',') + 1, LEN(@products)) select @count = @count + ' ' + convert(varchar,fid) from tbl_table where [type] =@individual END ELSE BEGIN SET @individual = @products SET @products = NULL select @count = @count + ' ' + convert(varchar,fid) from tbl_table where [type] =@individual select @count END END
这篇关于如何将行转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!