Solved

sql view or query to reformat data

Posted on 2014-01-01
5
193 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

628 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