SQL - Is it possible to make custom column by row values?

Question

Does anyone know if it's possible to turn something like the table below.. While still being able to use a SELECT query.

+----+-----------+---------------+------------+
| id | listingId | value         | identifier |
+----+-----------+---------------+------------+
| 1  | 1a        | Alaskan Husky | race       |
| 2  | 1a        | High          | activity   |
| 3  | 1a        | White         | colour     |
| 4  | 1b        | Akita         | race       |
| 5  | 1b        | Medium        | activty    |
| 6  | 1b        | Grey          | colour     |
+----+-----------+---------------+------------+

To something like this, while still being able to use a select query..

+----+-----------+---------------+---------+--------+
| id | listingId | race          | activty | colour |
+----+-----------+---------------+---------+--------+
| 1  | 1a        | Alaskan Husky | High    | White  |
| 2  | 1b        | Akita         | Medium  | Grey   |
+----+-----------+---------------+---------+--------+

I also want to be able to search this new "table". Let's say, the user has picked a filter with something like this:

Race: Alaskan Husky
Activity: Medium, High

It should then return the listingId of 1a.

The reason being for this, is that I can't do a proper SELECT query, when all the values are placed in different rows..

As you can see, the listingId should be the variable that groups it all together, and makes the value in the identifier column, a new column.

The reason why I don't just use the second table as default, is because each listing can have different filters and filter groups. And I need to be able to select specific listings that meet a user specified filter.

Thanks.

You are trying to PIVOT. Search SO further, or directly the PIVOT operator at MS reference. (Or wait for an answer, it won't be long ;) ) — George Menoutis, Jul 06 '18 at 18:34
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Thom A, Jul 06 '18 at 18:37

score 2 · Accepted Answer · answered Jul 06 '18 at 18:37

2

You can use conditional aggregation :

select min(id) id, listingId, 
       max(case when [identifier] = 'race' then [value] end) race,
       max(case when [identifier] = 'activity' then [value] end) activity,
       max(case when [identifier] = 'colour' then [value] end) colour
from table t
group by listingId;

answered Jul 06 '18 at 18:37

Yogesh Sharma

49,870
5
26
52

As I mentioned in another answer, it would be nice if it could auto detect the value in the identifier column, with the respective value. And do a new select query with filter conditions. But I'll look into that myself now. Thanks for the suggestion though. – Anders Jul 06 '18 at 19:09
This is definitely the most readable answer there is. I edited your answer just a little bit, so it also does a new select statement, which I also asked for. It might be helpful for others, thanks again! – Anders Jul 06 '18 at 20:21

sniperd · Answer 2 · 2018-07-06T19:02:51.693

1

This might do the trick, just a few self joins. I think there a few ways you could accomplish this but for me this is pretty easy to read and maintain.

CREATE TABLE [dbo].[the_table](
    [id] [int] NULL,
    [listingId] [varchar](50) NULL,
    [value] [varchar](50) NULL,
    [identifier] [varchar](50) NULL
) ON [PRIMARY]
GO

INSERT INTO the_table (id, listingId, value, identifier)
VALUES  (1, '1a', 'alaskan huskey', 'race'),
        (2, '1a', 'high', 'activity'),
        (3, '1a', 'white', 'colour'),
        (4, '1b', 'akita', 'race'),
        (5, '1b', 'medium', 'activity'),
        (6, '1b', 'grey', 'colour')

SELECT * FROM 
(SELECT a.id, a.listingID, a.value AS race, b.value as activity, c.value as colour
FROM the_table a
INNER JOIN the_table b
ON a.listingId  = b.listingId 
INNER JOIN the_table c
ON a.listingId  = c.listingId 
WHERE a.identifier = 'race'
AND b.identifier = 'activity'
AND c.identifier = 'colour') AS t
WHERE t.colour = 'white'

Output:

edited Jul 06 '18 at 19:02

answered Jul 06 '18 at 18:39

sniperd

5,124
6
28
44

Thanks for the suggestion. I'm not really a huge fan of pre-defining variables, as the "identifier" values are not constant. Would it be possible to "auto-detect" the values, and add those as columns, with their respective value? Also, how would I go about running a new SELECT query up against this new table? Thank you. – Anders Jul 06 '18 at 18:59
1

You _could_ write some dynamic SQL to have it look up the column names based upon what you have in the `identifier` column, but that might turn into quite a mess to maintain :) – sniperd Jul 06 '18 at 19:01
1

I've updated the answer to show how you can do a sub query, hope that helps! – sniperd Jul 06 '18 at 19:03
Thanks for ur help as well mate, just got what I wanted :) – Anders Jul 06 '18 at 21:15

score 0 · Answer 3 · answered Jul 06 '18 at 18:43

0

You can achieve it using Pivot

DECLARE @MyTable TABLE (Id INT, listingId VARCHAR(20),Value  VARCHAR(20), identifier varchar(20))
INSERT INTO @MyTable VALUES
(1,'1a','Alaskan Husky' ,'race'),
(2,'1a','High' ,'activity'),
(3,'1a','White'  ,'colour'),
(4,'1b','Akita' ,'race'),
(5,'1b','Medium','activity'),
(6,'1b','Grey' ,'colour')

SELECT *
FROM
(
SELECT listingId, identifier,Value FROM @MyTable)t
PIVOT(MIN(Value)
      FOR identifier
      IN (race,activity,colour)
      )p

answered Jul 06 '18 at 18:43

Mittal Patel

2,732
14
23

Hey, thanks for the suggestion, unfortunately, your code is not valid. – Anders Jul 06 '18 at 19:00
Can you please let me know the reason why code is not valid? As I have tested it and it gives same output as you have shown in your question – Mittal Patel Jul 06 '18 at 19:02

SQL - Is it possible to make custom column by row values?

3 Answers3