Pivot query

Question

Pivot query

scottic 21

I am trying to generate a result set from below data

desired result set :

Dillon Silzer 60,726 Reputation points Volunteer Moderator

2022-07-07T17:45:59.207+00:00

What program/language are you trying to accomplish this in? In Excel, you can use the TRANSPOSE() function.

https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027

In Power BI (Power Query) you can use the following guide:

https://learn.microsoft.com/en-us/power-query/pivot-columns
scottic 21 Reputation points

2022-07-07T18:15:34.61+00:00

I am trying to do this in SQL server
Dillon Silzer 60,726 Reputation points Volunteer Moderator

2022-07-07T18:20:02.047+00:00

What type of SQL server? MySQL? Oracle? PostgreSQL? Microsoft SQL?
scottic 21 Reputation points

2022-07-07T18:38:31.547+00:00

Microsoft SQL Server. if I am not able to achieve in MS SQL then will try in Python
Yitzhak Khabinsky 27,091 Reputation points

2022-07-07T19:44:52.087+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

All within the question, no images.

Answer accepted by question author

3 additional answers

Your answer

Dillon Silzer 60,726 Reputation points Volunteer Moderator

2022-07-07T17:45:59.207+00:00

What program/language are you trying to accomplish this in? In Excel, you can use the TRANSPOSE() function.

https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027

In Power BI (Power Query) you can use the following guide:

https://learn.microsoft.com/en-us/power-query/pivot-columns
scottic 21 Reputation points

2022-07-07T18:15:34.61+00:00

I am trying to do this in SQL server
Dillon Silzer 60,726 Reputation points Volunteer Moderator

2022-07-07T18:20:02.047+00:00

What type of SQL server? MySQL? Oracle? PostgreSQL? Microsoft SQL?
scottic 21 Reputation points

2022-07-07T18:38:31.547+00:00

Microsoft SQL Server. if I am not able to achieve in MS SQL then will try in Python
Yitzhak Khabinsky 27,091 Reputation points

2022-07-07T19:44:52.087+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

All within the question, no images.

Answer 1

Erland Sommarskog 129.1K MVP Volunteer Moderator

No, this should not be done in SQL. It's not uncommon to do dynamic pivots in SQL Server, although it is a non-relational operation. But you are taking things one step further by having multiple columns for the same header value.

Yes, I am sure we can write some ugly dynamic SQL for this, but you are looking for a presentational device, and that belongs in the presentation layer. So have SQL Server return a relational result set, and let Python or whatever do the formatting.

Answer 2

Dillon Silzer 60,726 Volunteer Moderator

The following steps and code should be able to help you achieve Transposing (PIVOT) the table in Microsoft SQL

https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/

--------------------------

If this answer has helped please don't forget to mark as answer. Have a good day.

Answer 3

LiHong-MSFT 10,061

Hi @scottic
I don't think it's a good choice to use PIVOT in SQL Sever, cause when you use PIVOT, you need to do some aggregate calculation which is no need in your issue.
BTW, not sure why you want to get a result table like this, wouldn't it make more sense to have a table like the one below?

Best regards,
LiHong

Answer 4

scottic 21

I give up doing this in SQL. I am now trying it in Python. Thanks guys for the suggestions

Share via

Pivot query

3 additional answers

Your answer