Creating columns from a select query

Question

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

This doesn't actually have anything to do with MySQL does it? — Strawberry
– Strawberry, Commented Aug 29, 2013 at 12:37

bummi · Accepted Answer · 2013-08-29 12:55:53Z

1

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'

edited Aug 29, 2013 at 12:55

answered Aug 29, 2013 at 12:31

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1546143 Over a year ago

I placed the result sent as a screen shot to the top. Its duplicating some data...

user1546143 Over a year ago

So e.g. PO 005331 had to changes 1. @ time 9530687 from PreviousForeign Price 85 to 114 and then @ time 9533838 changed from PreviousForeign Price 114 to 28.61 and some of ur return results don't correspond.

bummi Over a year ago

@user1546143 sorry, did not read your data carefully enough, forgot some conditions for the joins.

sigi · Accepted Answer · 2013-08-29 12:27:08Z

0

You can just use 'AS' to rename your columns

SELECT something AS somethingelse

answered Aug 29, 2013 at 12:27

sigi

10912 bronze badges

Collectives™ on Stack Overflow

Creating columns from a select query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related