SQL Server - show one column's values into single row with new columns

Question

I want to show values from 6 rows for a particular column in single row with 6 columns for 6 values..

Actually we have a column (TestValues) where we are storing 6 values split by '##@##'. But we want it to be shown in different textboxes on UI, but we have to split them in SQL Server stored procedure only.

Current Output using this query:

SELECT Code,Reason, 
LTRIM(RTRIM(m.n.value('.[1]','varchar(500)'))) AS TestValues
FROM
(
SELECT Code,Reason, CAST('<XMLRoot><RowData>' + REPLACE(TestValues,'##@##','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM dbo.Information where Logged_ID = 1001
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

Desired Output:

I have tried below query but it's not working ... Please let me know where I am wrong.

select * from 
(   
    select *, rank() over (partition by TestValues order by code) rank from
    (
        SELECT Code,Reason, 
        LTRIM(RTRIM(m.n.value('.[1]','varchar(500)'))) AS TestValues
        FROM
        (
        SELECT Code,Reason, CAST('<XMLRoot><RowData>' + REPLACE(TestValues,'##@##','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM dbo.Information where Logged_ID = 1001
        )t
        CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
    ) r
)tab1
pivot
(
max([TestValues]) for rank in ([1],[2],[3],[4],[5],[6])) pv

Where are those column headings coming from, and how are you determining the order of the results (I see no ID/Lable column for example)? To be very specific, what determines the order of your column TestValues is 12345,23456,'',123456,1234567,12345? I can't think of have ORDER BY clause that would be able to create that order (especially as 12345 is both at the end and start). Also, please don't post data as an image, provide it as text. You have enough reputation to know that. 😊 — Thom A
– Thom A ♦, Commented Aug 24, 2018 at 8:33
If you are asking about the desired o/p image.. the headers are just text I put. I need order as same as in current o/p image. And it was quicker to post images..(I know though). — Akshay
– Akshay, Commented Aug 24, 2018 at 8:39
Then, how do you achieve that order with an ORDER BY clause? With the data in that image it would be impossible, thus which column a value would be assigned to would be completely random. — Thom A
– Thom A ♦, Commented Aug 24, 2018 at 8:43

Thom A · Accepted Answer · 2018-08-24 09:11:58Z

1

As I said in the comments, currently, as it stands, the order you'll get is completely random, due to a lack of identity, ordering, label. Thus, you could use this, but the results will change every time the query is run:

USE Sandbox;
GO

CREATE TABLE dbo.SampleData(Code varchar(15),
                            Reason varchar(4),
                            testValues varchar(7));
GO
INSERT INTO dbo.SampleData (Code,
                            Reason,
                            testValues)
VALUES ('Phlebotomist','test','12345'),
       ('Phlebotomist','test','23456'),
       ('Phlebotomist','test',''),
       ('Phlebotomist','test','123456'),
       ('Phlebotomist','test','1234567'),
       ('Phlebotomist','test','12345');
GO
WITH RNs AS(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Code, Reason ORDER BY NEWID()) AS RN --PARTITION BY is a guess, NEWID as order is random as currently stands 
    FROM dbo.SampleData)
SELECT Code, Reason,
       MAX(CASE RN WHEN 1 THEN RNs.testValues END) AS BusinessUnit,
       MAX(CASE RN WHEN 2 THEN RNs.testValues END) AS MainUnit,
       MAX(CASE RN WHEN 3 THEN RNs.testValues END) AS [Location],
       MAX(CASE RN WHEN 4 THEN RNs.testValues END) AS CustID
FROM RNs
GROUP BY Code, Reason;
GO

GO
DROP TABLE dbo.SampleData;

This easy to fix though, provided that the order of the entries is always the same. Add an IDENTITY column (you might use something else, but you nned something to be able to determine the order):

CREATE TABLE dbo.SampleData(SomeID int IDENTITY(1,1), --Something to ORDER BY!
                            Code varchar(15),
                            Reason varchar(4),
                            testValues varchar(7));
GO
INSERT INTO dbo.SampleData (Code,
                            Reason,
                            testValues)
VALUES ('Phlebotomist','test','12345'),
       ('Phlebotomist','test','23456'),
       ('Phlebotomist','test',''),
       ('Phlebotomist','test','123456'),
       ('Phlebotomist','test','1234567'),
       ('Phlebotomist','test','12345');
GO
WITH RNs AS(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Code, Reason ORDER BY SomeID ASC) AS RN --NOw we have an order we can rely on!
    FROM dbo.SampleData)
SELECT Code, Reason,
       MAX(CASE RN WHEN 1 THEN RNs.testValues END) AS BusinessUnit,
       MAX(CASE RN WHEN 2 THEN RNs.testValues END) AS MainUnit,
       MAX(CASE RN WHEN 3 THEN RNs.testValues END) AS [Location],
       MAX(CASE RN WHEN 4 THEN RNs.testValues END) AS CustID
FROM RNs
GROUP BY Code, Reason;

GO
DROP TABLE dbo.SampleData;

Don't forget, data in a table is stored in a heap. It has no "inherent order".

edited Aug 24, 2018 at 9:11

answered Aug 24, 2018 at 8:54

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thom A Over a year ago

@IdontKnowEnglish huh?

Pugal Over a year ago

I written query that matches with your query.

Akshay Over a year ago

Thanks @Larnu Really appreciate it. I created a temp table inside stored proc.. Working well.

IVNSTN · Accepted Answer · 2018-08-24 08:38:14Z

Why don't you try to look at this intermediate select?

select *, rank() over (partition by TestValues order by code) rank from
    (
        SELECT Code,Reason, 
        LTRIM(RTRIM(m.n.value('.[1]','varchar(500)'))) AS TestValues
        FROM
        (
        SELECT Code,Reason, CAST('<XMLRoot><RowData>' + REPLACE(TestValues,'##@##','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM dbo.Information where Logged_ID = 1001
        )t
        CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
    ) r

There is the answer in the rank value - it is 1 for every row. That is why only group [1] in your pivot is filled with data (note, there is a correct max value as you defined; note, max from "strings" not numbers).

partition by TestValues - partition restarts ordering for each TestValues value. "Partitions" means "groups" here. Rank per group, row number per group and so on. If partitioning is applied to unique column each row will have rank/rk = 1 - because ranking/rn would restart for every row (every partition).

Here is an example of different window functions applied to dataset like yours: http://sqlfiddle.com/#!18/9eecb/31103

One of them will fit your needs.

By the way, "rank" function is supposed to give same result for different rows in a partition sometimes. Take a look at the difference between rank and rn function results for dup rows (see my comment at sqlfiddle).

And I'd take something else from dbo.Information to keep test results in the right order. Ordering by name (which is the same for every test in a group) or test results (which has nothing to do with test order) would possibly give each time different output for the same arguments and probably incorrect because of wrong ordering.

Collectives™ on Stack Overflow

SQL Server - show one column's values into single row with new columns

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related