Transform data in rows to columns

Question

I'm trying to transform data of the following form:

| ID | X | Y |
--------------
| 1  | a | m |
| 1  | b | n |
| 1  | c | o |
| 2  | d | p |
| 2  | e | q |
| 3  | f | r |
| 3  | g | s |
| 3  | h |   |

To this form:

| ID | X1 | X2 | X3 | Y1 | Y2 | Y3 |
------------------------------------
| 1  | a  | b  | c  | m  | n  | o  |
| 2  | d  | e  |    | p  | q  |    |
| 3  | f  | g  | h  | r  | s  |    |

What is the best way to accomplish this in SQL Server 2017? Is there a better way to do transformations like this using another tool?

https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/ — Eray Balkanli, Feb 08 '18 at 15:03
Possible duplicate of [Understanding PIVOT function in T-SQL](https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql) — Greg Viers, Feb 08 '18 at 15:10

score 0 · Answer 1 · answered Feb 08 '18 at 21:49

I don't think you can solve this problem on the DB side. You should do some backend programming. You would be able to use Pivot function, if you wanted to reverse your row values as column but you want to group them based on duplicate ids. I would solve this problem by checking duplicates by using the query below. At the results of that query, you'll be able to get max count for duplicated id. For example 1 duplicated 3 times, so you need to create a data table with 3x2+1=7 columns in your backend code. 1 stands for id column. After that you can just fill that table by checking data for each id.

WITH Temp (id, count)
AS
( 
Select id, count(*)
from MyTable
group by id
having count(*)>1
)
select max(count) from Temp

Transform data in rows to columns

1 Answers1