CODEWITHSUNDEEP
sqlsql-server
user1546143
user1546143

Reputation: 143

Creating columns from a select query

Sample Data

SELECT TOP 20
        TMPPO.PurchaseOrder ,
        TMPPO.LineItem ,
        ASLD.SignatureDate ,
        ASLD.SignatureTime ,
        ASLD.Operator ,
        ASLD.Variable ,
        ASLD.VariableDesc ,
        ASLD.VarNumericValue
FROM    #POAMENDMENTS TMPPO
        LEFT OUTER JOIN [SysproCompanyR].[dbo].[AdmSignatureLogDet] ASLD ON TMPPO.TransactionId = ASLD.TransactionId
                                                              AND TMPPO.SignatureDate = ASLD.SignatureDate
                                                              AND TMPPO.SignatureTime = ASLD.SignatureTime
WHERE   YEAR(TMPPO.SignatureDate) = 2013
        AND MONTH(TMPPO.SignatureDate) = 08
        AND VariableDesc IN ( 'Previous foreign price', 'Previous price',
                              'Foreign price', 'Price' )
ORDER BY PurchaseOrder ,
        LineItem

I have the following table but don't want to return the records as per below. Under the column heading Variable Desc I have Foreign Price, Previous foreign pirce, previous price and price I would like to make these as headings the replace Variable, Variable Desc and VarNumberic.
So e.g. for the first line would be 

Purchase Order LineItem SignatureDate SignatureTime Operator PrevFPrice FPrice PrevPrice Price
002074         0001     2013-02-23     9523598       UPOFA0  19.68      21.51   19.68    21.51
004931         0001     2013-08-09     7485253       PVWYK0  980.00     840.00  980.00   840.00

Sorry but is difficult to put sample data here no idea how to...

Is this possible?

@Bummi it provides me data like this, why is Purchase Order 005331 duplicating so many times when in essence according to the original sample data it changed only 2 times according to date and time enter image description here

Upvotes: 0

Views: 73

Answers (2)

bummi
bummi

Reputation: 27384

From what I am understanding you are looking for a join over your first query

;With CTE as
(
SELECT TOP 20 TMPPO.PurchaseOrder, TMPPO.LineItem, ASLD.SignatureDate,ASLD.SignatureTime,ASLD.Operator, ASLD.Variable, ASLD.VariableDesc, ASLD.VarNumericValue  FROM #POAMENDMENTS TMPPO
LEFT OUTER JOIN [SysproCompanyR].[dbo].[AdmSignatureLogDet] ASLD ON TMPPO.TransactionId = ASLD.TransactionId and TMPPO.SignatureDate = ASLD.SignatureDate and TMPPO.SignatureTime = ASLD.SignatureTime   
WHERE YEAR(TMPPO.SignatureDate) = 2013
and MONTH(TMPPO.SignatureDate) = 08 
and VariableDesc IN ('Previous foreign price','Previous price','Foreign price','Price')
ORDER BY PurchaseOrder, LineItem  
)
Select c1.PurchaseOrder,c1.LineItem,c1.SignatureDate,c1.SignatureTime,c1.Operator
,c1.VarNumericValue as [Previous foreign price]
,c2.VarNumericValue as [Previous price]
,c3.VarNumericValue as [Foreign price]
,c4.VarNumericValue as [Price]

FROM CTE c1
JOIN CTE c2 on c2.PurchaseOrder=c1.PurchaseOrder and c2.VariableDesc='Previous price'    
               and c2.LineItem=c1.LineItem and c2.SignatureDate=c1.SignatureDate and c2.SignatureTime=c1.SignatureTime
JOIN CTE c3 on c3.PurchaseOrder=c1.PurchaseOrder and c3.VariableDesc='Foreign price'    
               and c3.LineItem=c1.LineItem and c3.SignatureDate=c1.SignatureDate and c3.SignatureTime=c1.SignatureTime
JOIN CTE c4 on c4.PurchaseOrder=c1.PurchaseOrder and c4.VariableDesc='Price'    
               and c4.LineItem=c1.LineItem and c4.SignatureDate=c1.SignatureDate and c4.SignatureTime=c1.SignatureTime
Where c1.VariableDesc='Previous foreign price'

Upvotes: 1

sigi
sigi

Reputation: 99

You can just use 'AS' to rename your columns

SELECT something AS somethingelse

Upvotes: 0

Related Questions