CODEWITHSUNDEEP
sqlsql-server
Pawan Pillai
Pawan Pillai

Reputation: 2065

SQL Query to transpose data

I have the following table in SQL Server 2008:

ID  Type    Value
6   A_Left  A
6   B_Left  B
6   C_Left  C
6   D_Left  D
6   E_Left  E
6   A_Right A
6   B_Right B
6   C_Right C
6   D_Right D
6   E_Right E

And I need to get them grouped based on Value column like:

ID  Type    Type    Value
6   A_Left  A_Right A
6   B_Left  B_Right B
6   C_Left  C_Right C
6   D_Left  D_Right D
6   E_Left  E_Right E

Is this possible? I am not getting exact terms on what such transpose is called to google.

Upvotes: 2

Views: 72

Answers (2)

Sanjeev Singh
Sanjeev Singh

Reputation: 4066

Using PATINDEX :

SELECT t1.ID, t1.Type As Type, t2.Type AS Type, t1.Value As Value 
FROM t as t1 JOIN t t2 ON t1.ID=t2.ID AND t1.Value=t2.Value
WHERE PATINDEX('%_Left',t1.Type)>0 
AND PATINDEX('%_Right',t2.Type)>0

Similar to above but Using CTE AND LIKE (PATINDEX Can also be used) :

WITH CTE AS
(
    Select ID,Type,Value FROM t WHERE Type LIKE '%_Left'
)

SELECT t1.ID,CTE.Type+ ',' + t1.Type As Type,t1.Value FROM CTE
JOIN t t1
ON t1.ID = CTE.ID AND t1.Value = CTE.Value 
WHERE  t1.Type LIKE '%_Right'

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270599

Using an INNER JOIN, join the table against itself. On one side of the join, limit to those matching _Left and on the other side, limit to those matching _Right.

SELECT
  l.ID,
  l.Type AS L_Type,
  r.Type AS R_Type,
  l.Value
FROM
  t l
  INNER JOIN t r ON l.ID = r.ID AND l.Value = r.Value
WHERE
  RIGHT(l.type, 4) = 'Left'
  AND RIGHT(r.type, 5) = 'Right'

Here's a demonstration: http://sqlfiddle.com/#!6/f6590/4

Alternatively, you may use LIKE rather than the RIGHT() string function:

SELECT
  l.ID,
  l.Type AS L_Type,
  r.Type AS R_Type,
  l.Value
FROM
  t l
  INNER JOIN t r ON l.ID = r.ID AND l.Value = r.Value
WHERE
  l.type LIKE '%_Left'
  AND r.type LIKE '%_Right'

http://sqlfiddle.com/#!6/f6590/5

Upvotes: 1

Related Questions