英文:
Assistance with PIVOT function
问题
DECLARE @T TABLE (ID INT, Col VARCHAR(100), Description VARCHAR(50))
INSERT INTO @T VALUES (1, 'MV-RUC-DEBT-ASSESS', 'MV Debt Assessment');
WITH cte AS (
SELECT a.ID,
REPLACE(a.Col, '-', ' ') AS "Col",
a.Description
FROM @T a
),
cte2 AS (
SELECT a.ID,
n.r.value('.', 'varchar(50)') AS "Value",
a.Description
FROM cte a
CROSS APPLY (SELECT CAST('<r>'+REPLACE(REPLACE(Col, '&', '&'), ' ', '</r><r>')+'</r>' AS XML)) AS S(XMLCol)
CROSS APPLY S.XMLCol.nodes('r') AS n(r)
)
SELECT *
FROM cte2 a
PIVOT
(MAX(Value) FOR a.ID IN ([1], [2], [3], [4])) AS "Pivot";
英文:
I have a reference table that's currently delimited with '-' that I'm needing to split the values out into multiple columns. Our version of SQL DB doesn't support the string_split function.
The first part of the script (multiple CTE) is returning the results into multiple rows, which I'm then wanting to pivot into columns.
Is someone able to please assist with the PIVOT portion (or even a new statement if it achieves the same result). Am looking to have the results returned per final table format?
Thanks
Original Data:
| ID | Value | Description |
|---|---|---|
| 1 | MV-RUC-DEBT-ASSESS | MV Debt Assessment |
declare @T table (ID int, Col varchar(100), description varchar(50))
insert into @T values (1, 'MV-RUC-DEBT-ASSESS', 'MV Debt Assessment')
;
with cte as (
select a.ID
,replace(a.Col,'-', ' ') as "Col"
, a.description
from @T a
),
cte2 as (
select a.ID
, n.r.value('.', 'varchar(50)') "Value"
, a.description
from cte a
cross apply (select cast('<r>'+replace(replace(Col,'&','&amp;'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
cross apply S.XMLCol.nodes('r') as n(r)
)
select *
from cte2 a
pivot
(max(value) for a.ID in ([1], [2], [3], [4])) as "Pivot"
I'm expecting the results to look like
| ID | Description | 1 | 2 | 3 | 4 |
|---|---|---|---|---|---|
| 1 | MV Debt Assessment | MV | RUC | DEBT | ASSESS |
答案1
得分: 1
当您在使用a.ID作为旋转的一部分时，它只有一个，因此您只会获得值的最大值。
添加一个行号将为您提供所需的结果。
declare @T table (ID int, Col varchar(100), description varchar(50))
insert into @T values (1, 'MV-RUC-DEBT-ASSESS', 'MV Debt Assessment')
;
with cte as (
select a.ID
,replace(a.Col,'-', ' ') as "Col"
, a.description
from @T a
),
cte2 as (
select a.ID
, n.r.value('.', 'varchar(50)') "Value"
, row_number() OVER(PARTITION BY a.ID ORDER BY a.ID) rn
, a.description
from cte a
cross apply (select cast('<r>'+replace(replace(Col,'&','&'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
cross apply S.XMLCol.nodes('r') as n(r)
)
select *
from cte2 a
pivot
(max(value) for a.rn in ([1], [2], [3], [4])) as "Pivot";
| ID | description | 1 | 2 | 3 | 4 |
|---|---|---|---|---|---|
| 1 | MV Debt Assessment | MV | RUC | DEBT | ASSESS |
英文:
When you use a.ID as part of the pivot, it has only ones so that you only would get the max value of value.
adding a row_number would give you the wanted result
declare @T table (ID int, Col varchar(100), description varchar(50))
insert into @T values (1, 'MV-RUC-DEBT-ASSESS', 'MV Debt Assessment')
;
with cte as (
select a.ID
,replace(a.Col,'-', ' ') as "Col"
, a.description
from @T a
),
cte2 as (
select a.ID
, n.r.value('.', 'varchar(50)') "Value"
, row_number() OVER(PARTITION BY a.ID ORDER BY a.ID) rn
, a.description
from cte a
cross apply (select cast('<r>'+replace(replace(Col,'&','&'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
cross apply S.XMLCol.nodes('r') as n(r)
)
select *
from cte2 a
pivot
(max(value) for a.rn in ([1], [2], [3], [4])) as "Pivot"
| ID | description | 1 | 2 | 3 | 4 |
|---|---|---|---|---|---|
| 1 | MV Debt Assessment | MV | RUC | DEBT | ASSESS |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论