How to query columns for each entry value

Question

I have a TSQL table that contains a number of columns, where I would like to focus on 'RegID', 'Configuration' and 'Value'. Several 'Configuration's can contain a 'RegID', and only one 'Value' exists for a given 'Configuration'+'RegID' combination.

I would like to query a table that show rows of 'RegID' and columns of 'Configuration' listing 'Value' for each 'RegID' and 'Configuration', if any.

I have tried to solve it with the following query, but it just feels wrong to add all configurations manually. There can be up to 9 configurations.

SELECT   DISTINCT sp.RegID, 
         (
          SELECT  sp1.Value 
          FROM    StandardParameters sp1 
          WHERE   sp.RegID = sp1.RegID 
          AND     sp1.Configuration = 'conf1'
         ) AS Conf1,
         (
          SELECT  sp1.Value 
          FROM    StandardParameters sp1 
          WHERE   sp.RegID = sp1.RegID 
          AND     sp1.Configuration = 'conf2'
         ) as Conf2
FROM     StandardParameters sp

Any idea on how to optimize this so I don't have to manually list all the configurations? I have the feeling I am missing something obvious. May be related to group-by, but I can't figure out how to get the grouped values as columns. :-)

Sample Data
RegID   Configuration   Value
reg1    conf1           1
reg2    conf1           2
reg1    conf2           3
reg3    conf2           4

Desired Output
        conf1   conf2
reg1    1       3
reg2    2       NULL
reg3    NULL    4

Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) — Zohar Peled, Apr 16 '19 at 05:56
Looks like you are looking for [dynamic pivot](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Zohar Peled, Apr 18 '19 at 07:12

score 1 · Answer 1 · answered Apr 16 '19 at 06:17

1

You can handle this using case statement. Since there are only 9 configuration you can hardcode them.

select distinct sp.RegID, 
    case when sp.Configuration = 'conf1' then  sp1.Value end conf1, 
    case when sp.Configuration = 'conf2' then  sp1.Value end conf2
from StandardParameters sp

Sample data and desired results would help to understand exact requirement.

answered Apr 16 '19 at 06:17

Rima

1,447
1
6
12

This variant is wrong because it returns all the rows. Need to use `GROUP BY` or `PIVOT` here. – Sergey Menshov Apr 16 '19 at 06:40
I have used distinct instead of group by. – Rima Apr 16 '19 at 06:43
Run your query with the following test data and you will see about what I said: `CREATE TABLE StandardParameters( RegID int, Configuration varchar(10), Value varchar(10) ) INSERT StandardParameters VALUES (10,'conf1','111'), (10,'conf2','222'), (20,'conf1','333'), (20,'conf2','444')` – Sergey Menshov Apr 16 '19 at 06:44

score 1 · Answer 2 · answered Apr 16 '19 at 06:36

I think you can use 2 ways

-- with GROUP BY
SELECT
  RegID,
  MIN(CASE WHEN Configuration='conf1' THEN Value END) conf1,
  MIN(CASE WHEN Configuration='conf2' THEN Value END) conf2
FROM StandardParameters
GROUP BY RegID

-- wiht PIVOT
SELECT *
FROM StandardParameters PIVOT(MIN(Value) FOR Configuration IN([conf1],[conf2])) p

How to query columns for each entry value

2 Answers2