SQL Server query unpivot

Question

SQL Server query unpivot

NeophyteSQL 241

The data in the column is in the form of rows like below . How to convert to columns

RIGHT ankle:

Migration Percentage (MP): 36.6%

Previous MP: NA%

NULL

LEFT ankle:

Migration Percentage (MP): 35.2%

Previous MP: NA%

the expected result is in the column values below and the column names as below

right ankle migration pct Left Ankle Migration Pct

36 % 35.2%

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-02-27T22:30:51.6333333+00:00

Is that data in a single column? Or is that supposed to be multiple columns? The post is difficult to read.

It would help if you had posted CREATE TABLE + INSERT statements with sample data, so that we understand better.
NeophyteSQL 241 Reputation points

2024-02-27T23:26:14.34+00:00

Thank you very much for the reply Yes, it is single column. The x ray results for each patient are displayed in multiple rows using a single column. The data shown is for one such patient. I would like the output to be for each of the patients ColumnNMRightAnnklePCt ColumnNMLeftanklePCT Value Value
LiHongMSFT-4306 31,566 Reputation points

2024-02-28T02:33:35.4166667+00:00

Hi @NeophyteSQL To be honest, I didn't understand what you mean.

You need to know that we don't know your table structure or data, which is only visible to you. Could you please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO …) along with your expected result? So that we’ll get a right direction and make some test.

1 answer

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-02-27T22:30:51.6333333+00:00

Is that data in a single column? Or is that supposed to be multiple columns? The post is difficult to read.

It would help if you had posted CREATE TABLE + INSERT statements with sample data, so that we understand better.
NeophyteSQL 241 Reputation points

2024-02-27T23:26:14.34+00:00

Thank you very much for the reply Yes, it is single column. The x ray results for each patient are displayed in multiple rows using a single column. The data shown is for one such patient. I would like the output to be for each of the patients ColumnNMRightAnnklePCt ColumnNMLeftanklePCT Value Value
LiHongMSFT-4306 31,566 Reputation points

2024-02-28T02:33:35.4166667+00:00

Hi @NeophyteSQL To be honest, I didn't understand what you mean.

You need to know that we don't know your table structure or data, which is only visible to you. Could you please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO …) along with your expected result? So that we’ll get a right direction and make some test.

Answer 1

I'm afraid that you lose. Recall that a table is a set, that is, an unordered object. So what you have are just some dotted random unrelated annotations. There are two ankles, there are two migration percentages and some more junk. But there is nothing that says which migration percentages is related to which ankle. If there is any relation to any ankle at all, that is.

You need to go back to the data source and make sure that data is stored in a manageable way that upholds any relations that might have been there initially.

Share via

SQL Server query unpivot

1 answer

Your answer