How to query a SQL table

Question

I have a table that looks something like this:

IDPerson	Name	Type	Value
D601108B-8A37-4BD2-BB6F-0012A11BD929	FirstName	NULL	Bernard
D601108B-8A37-4BD2-BB6F-0012A11BD929	InternalNumber	NULL	Emp-001
D601108B-8A37-4BD2-BB6F-0012A11BD929	Name	NULL	Fish
D601108B-8A37-4BD2-BB6F-0012A11BD929	ValidFrom	System.DateTime	10/20/2020 00:00:00
D601108B-8A37-4BD2-BB6F-0012A11BD929	ValidTo	System.DateTime	10/31/2025 00:00:00

My question is how do I write a query to pull all of this information into a table that essentially has the column headings from the "Name" column, ie FirstName etc so that I have one row for all of the information relating to Bernard?

You should tag your question with db vendor you are using. It is generally recommended for any SQL question, especially important in case of your question. What are you looking for is usually called pivoting. Some vendors provide convenient syntax support. Or you can compose query as subselects for every column. — Tomáš Záluský, Jul 29 '21 at 22:51
Does this answer your question? [SQL query to pivot a column using CASE WHEN](https://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when) — Stu, Jul 29 '21 at 22:59

score 0 · Answer 1 · answered Jul 30 '21 at 05:35

If you are using MS SQL Server (guessing from .NET System.DateTime), you can use pivot clause:

with t (id,c,t,v) as (
select 'D601108B-8A37-4BD2-BB6F-0012A11BD929','FirstName'   ,NULL   ,'Bernard' union all
select 'D601108B-8A37-4BD2-BB6F-0012A11BD929','InternalNumber' ,NULL    ,'Emp-00' union all
select 'D601108B-8A37-4BD2-BB6F-0012A11BD929','Name'    ,        NULL   ,'Fish' union all
select 'D601108B-8A37-4BD2-BB6F-0012A11BD929','ValidFrom'   ,'System.DateTime'  ,'10/20/2020 00:00:00' union all
select 'D601108B-8A37-4BD2-BB6F-0012A11BD929','ValidTo'     ,'System.DateTime'  ,'10/31/2025 00:00:00'
)
select *
from (select c,v from t) as s
pivot (max(v) for c in ([FirstName], [InternalNumber], [Name], [ValidFrom], [ValidTo])) as p

(db fiddle)

Same principle with different syntax holds for Oracle.

In plain SQL, you can use case as stated in duplicate question candidate (unlike this question, that question operates with numbers, so it is not IMHO clear duplicate - use max function which ignores nulls):

with t (id,c,t,v) as (values
('D601108B-8A37-4BD2-BB6F-0012A11BD929','FirstName'     ,NULL   ,'Bernard'),
('D601108B-8A37-4BD2-BB6F-0012A11BD929','InternalNumber' ,NULL  ,'Emp-00'),
('D601108B-8A37-4BD2-BB6F-0012A11BD929','Name'  ,        NULL   ,'Fish'),
('D601108B-8A37-4BD2-BB6F-0012A11BD929','ValidFrom'     ,'System.DateTime'  ,'10/20/2020 00:00:00'),
('D601108B-8A37-4BD2-BB6F-0012A11BD929','ValidTo'   ,'System.DateTime'  ,'10/31/2025 00:00:00')
)
select max(case when c='FirstName' then v end)
     , max(case when c='InternalNumber' then v end)
     -- etc
from t
group by t.id

(db fiddle)

In Postgres it can be kind of simplified using max(v) filter (where c='FirstName') clause. Or crosstab extension.

How to query a SQL table

1 Answers1