April 28, 2017 at 2:02 pm
Good day,
I have a case where I need to reconstruct some data. I have built a way to take rows, put them into a column. This works well, EXCEPT, that the order is wrong. I need to get it to order by the KeyPosition Column, however all attempts to date have not worked.
declare @test-2 table (id int, grp varchar(25), keyposition int, KeyValue varchar(100));
Declare @desiredoutcome varchar(100) = 'hello - 4/28/2017 - 1381 - Ann - Example'
insert into @test-2 values (123, 'hello', 1, '4/28/2017');
insert into @test-2 values (123, 'hello', 2, '1381');
insert into @test-2 values (123, 'hello', 3, 'Ann');
insert into @test-2 values (123, 'hello', 4, 'Example');
with RebuildString_CTE as (
SELECT distinct ank.id, ltrim(rtrim(ank.grp)) + ' -' +
stuff(
(select distinct ' - ' + ank1.keyvalue
FROM @test ANK1
where ANK.id = id
for xml path('')),1,2,'') as ReconstructedAutoName
FROM @test ANK
)
Select case when dest.ReconstructedAutoName = @desiredoutcome
then 'Same'
else 'Different'
end as CaseResult
,dest.id, dest.ReconstructedAutoName, @desiredoutcome
from RebuildString_CTE Dest;
Above results
| CaseResult | id | ReconstructedAutoName | (No column name) |
| Different | 123 | hello - 1381 - 4/28/2017 - Ann - Example | hello - 4/28/2017 - 1381 - Ann - Example |
Notice, that dring the columns to rows, the date gets moved because 1 comes before 4. 🙁
Thoughts?
April 28, 2017 at 2:14 pm
April 28, 2017 at 2:19 pm
How about removing the DISTINCT and adding an ORDER BY ?DECLARE @test-2 AS TABLE (
id int,
grp varchar(25),
keyposition int,
KeyValue varchar(100)
);
INSERT INTO @test-2 VALUES (123, 'hello', 1, '4/28/2017');
INSERT INTO @test-2 VALUES (123, 'hello', 2, '1381');
INSERT INTO @test-2 VALUES (123, 'hello', 3, 'Ann');
INSERT INTO @test-2 VALUES (123, 'hello', 4, 'Example');
DECLARE @desiredoutcome AS varchar(100) = 'hello - 4/28/2017 - 1381 - Ann - Example';
WITH RebuildString_CTE as (
SELECT DISTINCT ank.id, LTRIM(RTRIM(ANK.grp)) + ' -' +
STUFF(
(
SELECT ' - ' + ank1.KeyValue
FROM @test-2 AS ANK1
WHERE ANK.id = id
ORDER BY ANK.id
FOR XML PATH('')
), 1, 2, '') AS ReconstructedAutoName
FROM @test-2 ANK
)
SELECT CASE WHEN dest.ReconstructedAutoName = @desiredoutcome THEN 'Same' ELSE 'Different' END AS CaseResult,
dest.id, dest.ReconstructedAutoName, @desiredoutcome
FROM RebuildString_CTE AS Dest;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 28, 2017 at 2:23 pm
Thanks for the response. I had tried that. Do you get it to work?
I get:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Which makes sense, but adding it alters my results.
April 28, 2017 at 2:25 pm
CoryEllingson - Friday, April 28, 2017 2:23 PMThanks for the response. I had tried that. Do you get it to work?I get:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.Which makes sense, but adding it alters my results.
Try creating a CTE which gets distinct data first, then reference the cte when building up the string
April 28, 2017 at 2:26 pm
CoryEllingson - Friday, April 28, 2017 2:23 PMThanks for the response. I had tried that. Do you get it to work?I get:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.Which makes sense, but adding it alters my results.
I took out the DISTINCT. Not sure you need it, but if you do, you can select out that DISTINCT data in a previous CTE and join to it in the one that uses the STUFF, or perhaps GROUP BY instead, but that will force an aggregate be applied to your STUFF.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 28, 2017 at 2:35 pm
This should work to replace the DISTINCT. It gives the correct result:DECLARE @test-2 AS TABLE (
id int,
grp varchar(25),
keyposition int,
KeyValue varchar(100)
);
INSERT INTO @test-2 VALUES (123, 'hello', 1, '4/28/2017');
INSERT INTO @test-2 VALUES (123, 'hello', 2, '1381');
INSERT INTO @test-2 VALUES (123, 'hello', 3, 'Ann');
INSERT INTO @test-2 VALUES (123, 'hello', 4, 'Example');
DECLARE @desiredoutcome AS varchar(100) = 'hello - 4/28/2017 - 1381 - Ann - Example';
WITH DISTINCT_VALUES AS (
SELECT DISTINCT T.id, T.keyposition, T.KeyValue
FROM @test-2 AS T
),
RebuildString_CTE as (
SELECT DISTINCT ank.id, LTRIM(RTRIM(ANK.grp)) + ' -' +
STUFF(
(
SELECT ' - ' + ank1.KeyValue
FROM DISTINCT_VALUES AS ANK1
WHERE ANK.id = id
ORDER BY ANK.keyposition
FOR XML PATH('')
), 1, 2, '') AS ReconstructedAutoName
FROM @test-2 ANK
)
SELECT CASE WHEN dest.ReconstructedAutoName = @desiredoutcome THEN 'Same' ELSE 'Different' END AS CaseResult,
dest.id, dest.ReconstructedAutoName, @desiredoutcome
FROM RebuildString_CTE AS Dest;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 28, 2017 at 5:01 pm
Thanks! That worked. I am not sure why I did not think of that - But I truly appreciate it!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply