SQL Select Columns WHERE value IN GROUP BY Clause BY Distinct

Question

If one has a grouped result set. ie:

(result set)

Id (bigint), UserId (int), Field1 (varchar), Field2 (varchar) ============================================================= 1, 22, 'Job', 'Teacher' 2, 22, 'Car', 'Mustang' 3, 22, 'Age', '35' 4, 47, 'Job', 'Clerk' 5, 47, 'Car', 'Corolla' 6, 47, 'Age', '23' 7, 85, 'Job', 'Banker' 8, 85, 'Car', 'Volkswagon' 9, 85, 'Age', '44'

How can one return a result set based on a unique column (in this case, UserId) and get Fields in a where clause based on specific case?

To Match This:

Id (bigint), UserId (int), Car (varchar), Job (varchar), Age (varchar) ====================================================================== 1, 22, 'Mustang', 'Teacher', '35' 2, 47, 'Corolla', 'Clerk', '23' 3, 85, 'Volkswagon', 'Banker', '44'

I know this is bad database design, but I am working with an old application. I have tried union or joining based on a separate select query with no avail.

Any help is appreciate as I am not proficient in SQL.

Is there a reason why you are using a key/value table instead of a normal table with columns for job, age and car? Key/value tables are a nuisance to work with in my opinion. — Thorsten Kettner
– Thorsten Kettner, Commented Oct 3, 2018 at 16:47
The id column in your result makes no sense. You should remove it. — Thorsten Kettner
– Thorsten Kettner, Commented Oct 3, 2018 at 16:48
@ThorstenKettner Like I said, I know it's bad database design. It's a bigger application that depends on a specific result set structure (until we can revise the table and source code itself). I agree they are a pain! — IntricateJake
– IntricateJake, Commented Oct 3, 2018 at 16:49

sticky bit · Accepted Answer · 2018-10-03 16:49:27Z

You can try and use conditional aggregation and the row_number() window function.

SELECT row_number() OVER (ORDER BY x.userid) id, * FROM (SELECT t.userid, max(CASE t.field1 WHEN 'Car' THEN t.field2 END) car, max(CASE t.field1 WHEN 'Job' THEN t.field2 END) job, max(CASE t.field1 WHEN 'Age' THEN t.field2 END) age FROM elbat t GROUP BY t.userid) x;

(And by the way, it's "Volkswagen", not "Volkswagon".)

Thorsten Kettner · Accepted Answer · 2018-10-03 16:44:54Z

You can group by userid and use conditional aggregation to get the values.

select userid, max(case when field1 = 'Car' then field2 end) as car, max(case when field1 = 'Job' then field2 end) as job, max(case when field1 = 'Age' then field2 end) as age from mytable group by userid order by userid;

Joel Coehoorn · Accepted Answer · 2018-10-03 16:39:31Z

You can join to the set an additional time for each extra field:

SELECT row_number() over (order by t1.UserId) t1.UserId, t1.Field2 As Car, t2.Field2 As Job, t3.Field2 As Age FROM [MyTable] t1 LEFT JOIN [MyTable] t2 ON t2.UserId = t1.UserID AND t2.Field1 = 'Job' LEFT JOIN [MyTable] t3 ON t3.UserId = t1.UserID AND t3.Field1 = 'Age' WHERE t1.Field1 = 'Car' ORDER BY t1.UserID

Probably the (currently deleted) conditional aggregation answer will work better, but it's nice to know your options.

rgen3 · Accepted Answer · 2018-10-03 16:41:18Z

You can use self join

select t1.Id, t1.UserId, t1.Field1, t2.Field2, t3.Field3 from table_name t1 left join table_name t2 on t1.UserId=t2.UserId and t2.Field1='Car' left join table_name t3 on t1.UserId=t3.UserId and t2.Field1='Age' where t1.Field1='job'

Olivier Jacot-Descombes · Accepted Answer · 2018-10-03 17:28:18Z

You can use SQL PIVOT

SELECT UserId, Job, Car, Age FROM (SELECT UserId, Field1, Field2 FROM dbo.mytable) x PIVOT ( MAX(Field2) FOR Field1 IN (Job, Car, Age) ) AS pvt

PIVOT converts rows into columns by taking a value (Field1) as name for the new columns and aggregating another value (Field2) as the value for these new columns.

Interesting addition, this is easier to read. How does it look in terms of performance?
Never tested it, but probably better than self made solutions.

Collectives™ on Stack Overflow

SQL Select Columns WHERE value IN GROUP BY Clause BY Distinct

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Related