Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

SQL Query problem

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
PhilippeRenaud
Asked:
PhilippeRenaud
  • 2
1 Solution
 
SANDY_SKCommented:
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
 
PhilippeRenaudAuthor Commented:
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
 
SANDY_SKCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now