将管道从行拆分为COLUMN [英] Split Pipeline from rows to COLUMN

问题描述

你能告诉我如何将行转换成列



例如;



'563 / 90，Chitragupta Nagar | Alambagh |勒克瑙|北方邦|印度| 226005'



应该是第1栏：563/90，第2栏：Chitragupta Nagar .. in sql



问候

dhana

Hi,

Could u please tel me how to convert rows into column

for eg;

'563/90, Chitragupta Nagar | Alambagh | Lucknow | Uttar Pradesh | India | 226005'

should be column 1: 563/90 ,column 2: Chitragupta Nagar.. in sql

Regards
dhana

推荐答案

测试它：

Test it:

DECLARE @string VARCHAR(255)
DECLARE @delimiter VARCHAR(1)

--DROP TABLE #tmp
CREATE TABLE #tmp (ID INT, ColID INT, MyValue VARCHAR(255))

DECLARE @t TABLE (ID INT IDENTITY(1,1), SomeData VARCHAR(300))
INSERT INTO @t (SomeData) 
SELECT '563/90, Chitragupta Nagar | Alambagh | Lucknow | Uttar Pradesh | India | 226005' AS SomeData
UNION ALL SELECT '564/91, Chitragupta Nagar | Balambagh | Mucknow | Vttar Rradesh | India | 226006'
UNION ALL SELECT '565/92, Dhitragupta Oagar | Calambagh | Nucknow | Wttar Sradesh | India | 226007'
UNION ALL SELECT '566/93, Ehitragupta Pagar | Dalambagh | Oucknow | Xttar Tradesh | India | 226008'
UNION ALL SELECT '563/94, Fhitragupta Qagar | Elambagh | Pucknow | Yttar Uradesh | India | 226009'

SET @delimiter ='|'
 
;WITH MyRows AS
(
	SELECT ID, 1 AS ColID, RTRIM(LTRIM(LEFT(SomeData, CHARINDEX(@delimiter, SomeData) -1))) AS MyValue, RIGHT(SomeData, LEN(SomeData) - CHARINDEX(@delimiter, SomeData)) AS Remainder
	FROM @t
	WHERE CHARINDEX(@delimiter, SomeData)>0
	UNION ALL
	SELECT ID, ColID+1 AS ColID, RTRIM(LTRIM(LEFT(Remainder, CHARINDEX(@delimiter, Remainder) -1))) AS MyValue, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(@delimiter, Remainder)) AS Remainder
	FROM MyRows
	WHERE CHARINDEX(@delimiter, Remainder)>0
	UNION ALL
	SELECT ID, ColID+1 AS ColID, RTRIM(LTRIM(Remainder)) AS MyRow, NULL AS Remainder
	FROM MyRows
	WHERE CHARINDEX(@delimiter, Remainder)=0
)
INSERT INTO #tmp (ID, ColID, MyValue)
SELECT ID, ColID, MyValue
FROM MyRows
 
DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)

SET @cols = STUFF((SELECT DISTINCT '],[Col' + CONVERT(VARCHAR(10),ColID)
					FROM #tmp
			FOR XML PATH('')),1,2,'') + ']'

SET @dt = N'SELECT ID, ''Col'' + CONVERT(VARCHAR(30), ColID) AS ColName, MyValue
	FROM #tmp'
--EXEC(@dt)

SET @pt = N'SELECT ID, ' + @cols + ' ' + 
'FROM(' + @dt + ') AS DT ' +
'PIVOT(MAX(MyValue) FOR ColName IN(' + @cols + ')) AS PT'
EXEC(@pt)

DROP TABLE #tmp

结果：

Result:

ID      Col1                            Col2            Col3          Col4      Col5    Col6
1	563/90, Chitragupta Nagar	Alambagh	Lucknow	Uttar Pradesh	India	226005
2	564/91, Chitragupta Nagar	Balambagh	Mucknow	Vttar Rradesh	India	226006
3	565/92, Dhitragupta Oagar	Calambagh	Nucknow	Wttar Sradesh	India	226007
4	566/93, Ehitragupta Pagar	Dalambagh	Oucknow	Xttar Tradesh	India	226008
5	563/94, Fhitragupta Qagar	Elambagh	Pucknow	Yttar Uradesh	India	226009

这篇关于将管道从行拆分为COLUMN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！