Solved

sql view or query to reformat data

Posted on 2014-01-01
5
192 Views
Last Modified: 2014-01-10
Hello Experts,

I am banging my head on what seems to be a simple query.  I need to massage the data into a different presentation.  I need to take the values of the input table and create column names for the output table.

Here's what the input table looks like:
      
ID      TYPE      Value
45      invoice      12345
45      check      555444
45      date              12/5/2013
46      invoice      987654
46      check      555666
46      date              12/3/2013

i need the output to look like this:
output                  
ID      invoice      check      date
45      12345      555444      12/5/2013
46      987654      555666      12/3/2013

thanks!!!
0
Comment
Question by:vinny3333
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39750370
You need to pivot the data, sorry don't have time to do it, but here is a reference http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
0
 
LVL 25

Accepted Solution

by:
chaau earned 250 total points
ID: 39750403
This is how you do it with PIVOT:
SELECT ID, 
[invoice], [check], [date]
FROM
(SELECT ID, Type, Value 
    FROM Table1) AS SourceTable
PIVOT
(
MAX(Value)
FOR Type IN ([invoice], [check], [date])
) AS PivotTable;

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 39752139
If there are a limited number of values for type  (e.g. 3 as in your example) -
select id,
max(case when type = 'invoice' then value end) as invoice,
max(case when type = 'check' then value end) as check_rcvd,
max(case when type = 'date' then value end) as somedate
from input_table
group by id;
0
 

Author Comment

by:vinny3333
ID: 39754866
Thx for the education!!  Both solutions will work for my needs.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question