CODEWITHSUNDEEP
sqlpivot
Shraddha Dharmik
Shraddha Dharmik

Reputation: 1

How to convert same column different rows to different column same row in SQL?

This is what I want to do.

----input table----

SID | VALUE
1   |  v1
1   |  v2
1   |  v3
1   |  v4
1   |  v5
2   |  s1
2   |  s2
2   |  s3

---output table----
sid | col1 | col2 | col3 | col4 | col5
1   |  v1  |  v2  |  v3  |  v4  |  v5
2   |  s1  |  s2  |  s3  |  ''  |  ''

Upvotes: 0

Views: 39

Answers (2)

JohnMathew
JohnMathew

Reputation: 528

Learn pivot

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

do something like

  select *
    from 
    (
      select sid, value
      from table_name
    ) src
    pivot
    (
      sid
      for sid in ([1], [2])
    ) piv;

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

The general pattern of a conditional aggregation:

SELECT
  sid,
  MAX(CASE WHEN value = 'v1' THEN value END) as col1,
  MAX(CASE WHEN value = 'v2' THEN value END) as col2,
  ...
FROM t
GROUP BY sid

Il leave it for you to put the other columns in as a practice:)

I prefer to put the value as the column name, not col1, col2 etc

Also if you really want empty strings rather than nulls for those last two columns you can modify the case when to have ELSE '' (note: won't work if you use MIN instead of max) or use COALESCE around the MAX

Upvotes: 1

Related Questions