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.
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'
LIKE.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'
