Solved

Pivot data

Posted on 2010-09-23
9
247 Views
Last Modified: 2012-05-10
I have the following SQL query which returns the results with each netting id on a seperate row.

SELECT     cparty.name, cpidents.name AS nettingid, paymethd.name AS type
FROM         cparty INNER JOIN
                      cpidents ON cparty.thekey = cpidents.cparty INNER JOIN
                      paymethd ON cpidents.paymethd = paymethd.thekey
WHERE     (paymethd.name = N'CNSID-LOANS') or (paymethd.name = N'CNSID-FX') or (paymethd.name = N'CNSID-ROYALTY')


name                                      nettingid                             type
3206772 Nova Scotia Co.      1428                                   CNSID-FX
3206772 Nova Scotia Co.      1428                                   CNSID-LOAN    
3206772 Nova Scotia Co.      1428                                   CNSID-ROYALTY    
                                   
I would like it displayed like this

name                                               CNSID-FX      CNSID-LOAN        CNSID-ROYALTY    
3206772 Nova Scotia Co.                1428             1428                    1428

       


             
0
Comment
Question by:Dept808
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 33746029
Here's an example using SQL Server's "pivot" - actually pretty close to your data.

http://www.mssqltips.com/tip.asp?tip=1019
0
 

Expert Comment

by:irenetheodora
ID: 33751440
I think this one should work fine.

SELECT     cparty.name,
      loans. nettingid as N'CNSID-LOANS',
      fx.nettingid as N'CNSID-FX',
      royalty.nettingid as N'CNSID-ROYALTY'
FROM        cparty
left join
  (select  cpidents.name as neetingid,
                cparty
      from  cpidents
      INNER JOIN paymethd
     ON cpidents.paymethd = paymethd.thekey
     where paymethd.name = N'CNSID-LOANS') loans
ON loans.cparty = cparty.thekey
left join
  (select     cpidents.name as neetingid,
                   cparty
      from     cpidents
      INNER JOIN paymethd
      ON cpidents.paymethd = paymethd.thekey
      where paymethd.name = N'CNSID-FX') fx
ON fx.cparty = cparty.thekey
left join
  (select     cpidents.name as neetingid,
                   cparty    
       from    cpidents
       INNER JOIN paymethd
      ON cpidents.paymethd = paymethd.thekey
       where paymethd.name = N'CNSID-ROYALTY') royalty
ON royalty.cparty = cparty.thekey
where
      loans. nettingid is not null and
      fx.nettingid is not null and
      royalty.nettingid is not null
   

0
 

Author Comment

by:Dept808
ID: 33753407
    loans.nettingid as N'CNSID-LOANS',
     fx.nettingid as N'CNSID-FX',
     royalty.nettingid as N'CNSID-ROYALTY'

Are not valid columns?
0
Independent Software Vendors: 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!

 
LVL 3

Expert Comment

by:xiong8086
ID: 33759433
maybe try this following:

SELECT name, 'CNSID-LOANS' AS CNSID-LOANS, 'CNSID-FX' AS CNSID-FX, 'CNSID-ROYALTY' AS CNSID-ROYALTY
FROM
(SELECT     cparty.name, cpidents.name AS nettingid, paymethd.name AS type
FROM         cparty INNER JOIN
                      cpidents ON cparty.thekey = cpidents.cparty INNER JOIN
                      paymethd ON cpidents.paymethd = paymethd.thekey
WHERE     (paymethd.name = N'CNSID-LOANS') or (paymethd.name = N'CNSID-FX') or (paymethd.name = N'CNSID-ROYALTY')
) p
PIVOT
(
COUNT (nettingid)
FOR type IN
( 'CNSID-LOANS', 'CNSID-FX', 'CNSID-ROYALTY' )
) AS pvt
0
 

Expert Comment

by:irenetheodora
ID: 33767110
Dept808: yes they're valid actually. or you think I should put out another way?
0
 

Author Comment

by:Dept808
ID: 33769980
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'p'.
0
 
LVL 3

Expert Comment

by:xiong8086
ID: 33776038
put all the string values in [ ] like ['CNSID-FX'], then try again?
0
 

Author Comment

by:Dept808
ID: 33778679
I was able to rework the syntax and get results with the following query.  However I dont want a count of the values.. there will always be 1 or 0  I want to display the actual value, similar to my example.  The issue I now have is the value in the nettingid field is a nchar..

SELECT name, [CNSID-LOANS] AS "CNSID-LOANS", [CNSID-FX] AS "CNSID-FX", [CNSID-ROYALTY] AS "CNSID-ROYALTY"
FROM
(SELECT     cparty.name, cpidents.name AS nettingid, paymethd.name AS type
FROM         cparty INNER JOIN
                      cpidents ON cparty.thekey = cpidents.cparty INNER JOIN
                      paymethd ON cpidents.paymethd = paymethd.thekey
WHERE     (paymethd.name = N'CNSID-LOANS') or (paymethd.name = N'CNSID-FX') or (paymethd.name = N'CNSID-ROYALTY')
) p
PIVOT
(
count(nettingid)
FOR type IN
("CNSID-LOANS", "CNSID-FX", "CNSID-ROYALTY" )
) AS pvt
0
 
LVL 3

Accepted Solution

by:
xiong8086 earned 500 total points
ID: 33781232
can try this?

SELECT name, ['CNSID-LOANS'] ,['CNSID-FX'] ,['CNSID-ROYALTY']
FROM
(SELECT     cparty.name, cpidents.name AS nettingid, paymethd.name AS type
FROM         cparty INNER JOIN
                      cpidents ON cparty.thekey = cpidents.cparty INNER JOIN
                      paymethd ON cpidents.paymethd = paymethd.thekey
WHERE     (paymethd.name = N'CNSID-LOANS') or (paymethd.name = N'CNSID-FX') or (paymethd.name = N'CNSID-ROYALTY')
) p
PIVOT
(
count(nettingid)
FOR type IN
('CNSID-LOANS', 'CNSID-FX', 'CNSID-ROYALTY' )
) AS pvt

can show the previous results?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

685 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