Solved

sql view or query to reformat data

Posted on 2014-01-01
5
191 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
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

679 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