Click here to Skip to main content
14,644,614 members
Rate this:
Please Sign up or sign in to vote.
See more:
How do i project 3 different columns from 3 rows without creating temp table.

In my table, I have 3 columns name type, identifier and value.

column 'type' has records 'myapp' in all 3 rows.

column 'identifier' has record 'app-owner' in first row.
column 'identifier' has record 'app-dev' in second row.
column 'identifier' has record 'app-tester' in third row.

column 'value' has record 'The Owner' in row 1.
column 'value' has record 'The Dev' in row 2.
column 'value' has record 'The Tester' in row 3.

How do i write query so that i can get data back with columns 'Owner', 'Dev' and 'Tester' with values 'The Owner', 'The Dev' and 'The Tester' ?

What I have tried:

I tried by creating temp table but that is not how I want to do it.

Self Join can produce the result but is it efficient way to do it when table contains many records?

Select TOP(1) as 'Owner', as 'Dev', as 'Tester FROM myTable m
JOIN myTable a on a.type = m.type and a.identifier = 'app-owner'
JOIN myTable b on b.type = m.type and b.identifier = 'app-dev'
JOIN myTable c on c.type = m.type and c.identifier = 'app-tester'
Updated 27-May-20 7:07am
MadMyche 27-May-20 9:56am
Please use the Improve Question widget and add-in what you expect the result set to look like.
istudent 27-May-20 10:12am
I want to project values from three rows as in three different columns. I do not know how to create table in Question so that i could show you. I did self join and I can produce the result but is there efficient way to do it?
Rate this:
Please Sign up or sign in to vote.

Solution 1

Kind sounds like you want to be using something like the PIVOT[^] function which is found in SQL Server or Excel.
This would give an output something like this
ColumnRow     AppOwner   AppDev   AppTester
============  =========  =======  =========
Column Title  The Owner  The Dev  The Tester
From this query
   Type       INT ,
   Identifier NVARCHAR(16) ,
   Value      NVARCHAR(16)

  (1, 'app-owner', 'The Owner')
, (2, 'app-dev', 'The Dev')
, (3, 'app-tester', 'The Tester')

SELECT ColumnRow = 'Column Title'
     , AppOwner  = [App-Owner]
     , AppDev    = [App-Dev]
     , AppTester = [App-Tester]

FROM  (  SELECT [Value], Identifier
         FROM @Temp
) as S
PIVOT  ( Max([Value])
         FOR [Identifier]
         IN  ([App-Owner], [App-Dev], [App-Tester])
) as P
Now if this is what you want and you are using a different version of SQL (eg MySql), you will need to search for similar functions depending on what your database is using (eg MySql PIVOT), and similar scripts will present themselves to you
When you look at this code on here (or in an SQL IDE) you will notice some of the column names (Type, Value) have a different color in the TABLE declaration and are also wrapped in [square brackets] in the query. This is because these are reserved/special words in many database servers. Generally it is recommended to rename these columns to prevent confusion to developers or the queries being run
istudent 27-May-20 12:38pm
Thank you sir. But I do not want to create temp table.
MadMyche 27-May-20 13:01pm
You don't- the @temp table was so I could work with the data you provided, and was for proof-of-concept.
You can replace that name with whatever naming your tables and columns have as needed
Rate this:
Please Sign up or sign in to vote.

Solution 2

Something like this might work:
    MAX(CASE identifier WHEN 'app-owner' THEN value END) As Owner,
    MAX(CASE identifier WHEN 'app-dev' THEN value END) As Dev,
    MAX(CASE identifier WHEN 'app-tester' THEN value END) As Tester

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100