Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query problem

Posted on 2012-03-12
3
Medium Priority
?
275 Views
Last Modified: 2012-08-14
Hello EE,

I have a query results like this:

Name       Level      Key      Description          Jannuary    February
 
Peter          3            P           Peter's Sales        500               200
Isabel         3            P           Isa's Sales            200               450


How could I do a new query the results be like this :

Key              Peter Jan   Peter Feb         Isa Jan    Isa Feb
 
 P                    500               200                200        450

(since both lines on first query was P)   there is only 1 row on second query result and Vendors are horizontal. If there were 5 months they would have 5 months each...

is it possible ? ... thanks sql experts !!
0
Comment
Question by:PhilippeRenaud
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
SANDY_SK earned 1500 total points
ID: 37711217
i will Take the result of you first query as table1 then the new query would be

select distinct Peter.Key, peter.Jan as Peter Jan  ,peter.feb as Peter Feb , Isa.jan as Isa Jan,  isa.feb as Isa Feb
FROM table1 as peter JOIN table1 as Isa ON peter.Level=Isa.Level
AND peter.Key=Isa.Key
AND peter.Name <> Isa.Name

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 37711235
Ok but is there a way to to this without hardcoding the names ?
because tomorrow it could have 4 name (peter isa john mike) and the query wouldnt work anymore.. ?
0
 
LVL 11

Expert Comment

by:SANDY_SK
ID: 37711381
Well if that is the case what you could do is use a stored procedure to construct the query in the same way. but then there will involve too many self joins, not too sure if that's a very efficient way.

There is one option of pivot tables but i don't thing it can be used in this scenario.

Exploring it, if i am able to get it will post it .
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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.

972 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