How do I project 3 different columns from 3 rows without creating temp table.

Question

0.00/5 (No votes)

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) a.app-owner as 'Owner', b.app-dev as 'Dev', c.app-tester 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'

Posted 27-May-20 3:35am

istudent

Updated 27-May-20 7:07am

v2

Add a Solution

Comments

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?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2020-05-27T07:08:00

Solution 2

Something like this might work:

SQL

SELECT
    [type],
    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
FROM
    myTable
GROUP BY
    [type]
;

Posted 27-May-20 7:08am

Richard Deeming

MadMyche · Answer 2 · 2020-05-27T04:54:00

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

SQL

DECLARE @Temp TABLE (
   Type       INT ,
   Identifier NVARCHAR(16) ,
   Value      NVARCHAR(16)
)

INSERT @Temp VALUES
  (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

Note:
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