[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Pivot data

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
Dept808
Asked:
Dept808
  • 3
  • 3
  • 2
  • +1
1 Solution
 
carsRSTCommented:
Here's an example using SQL Server's "pivot" - actually pretty close to your data.

http://www.mssqltips.com/tip.asp?tip=1019
0
 
irenetheodoraCommented:
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
 
Dept808Author Commented:
    loans.nettingid as N'CNSID-LOANS',
     fx.nettingid as N'CNSID-FX',
     royalty.nettingid as N'CNSID-ROYALTY'

Are not valid columns?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
xiong8086Commented:
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
 
irenetheodoraCommented:
Dept808: yes they're valid actually. or you think I should put out another way?
0
 
Dept808Author Commented:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'p'.
0
 
xiong8086Commented:
put all the string values in [ ] like ['CNSID-FX'], then try again?
0
 
Dept808Author Commented:
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
 
xiong8086Commented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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