June 14, 2016 at 4:16 am
Hi,
I have following table structure
Transaction (transactionId int,
Column2 varchar(10))
TransactionTracking (id int,
TransactionId int, -- FK_Transaction.transactionid
Name varchar(20),
Value varchar(20))
Transaction data
TransactionId Column2
123 aaa
234 3333
....
....
TransactionTracking
Id TransactionId name Value
1 123 CreatedBy me
2 123 CustomerId 1212
3 123 Accno 1w3w3e4
4 234 CreatedBy me22
5 234 CustomerId 12122
6 234 Accno 1w3w3e42
.....
I want to convert rows into columns for every transactionid.So that data may look like following
TransactionId CreatedBy CustomerId AccNo
123 me 1212 1w3w3e4
234 me2 121222 1w3w3e42
I wrote following sql to return results in pivot , it is showing columns but not placing values under them. please suggest what i have done wrong and what other way exist to accomplish it
SELECT * FROM
(
SELECT tr.transactionid,tr.transactionid as TranId,td.name ,td.VALUE
FROM Transaction tr
LEFT OUTER JOIN TransactionTracking td
ON tr.transactionid=td.transactionid
WHERE td.name in ('CustomerId','CreatedBy','AccNo')
)a
pivot
(
MAX(transactionid)
FOR name in (CustomerId,CreatedBy,AccNo
)p
June 14, 2016 at 5:01 am
Change MAX(transactionid) to MAX( Value ) in your query
The below method is another way to accomplish the same
SELECT TT.TransactionId,
MAX( CASE WHEN TT.name = 'CreatedBy' THEN TT.Value ELSE NULL END ) AS CreatedBy,
MAX( CASE WHEN TT.name = 'CustomerId' THEN TT.Value ELSE NULL END ) AS CustomerId,
MAX( CASE WHEN TT.name = 'Accno' THEN TT.Value ELSE NULL END ) AS Accno
FROM TransactionTracking AS TT
GROUP BY TT.TransactionId
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply