I am using SQL 2000 and i want to transpose rows into columns.
Before:
Type RowID Value
Date 1 "12/01/2007"
Val1 1 15
Val2 1 16
Val3 1 17
Date 2 "13/01/2007"
Val1 2 12
Val2 2 19
Val3 2 10
After:
Date Val1 Val2 Val3
"12/01/2007" 15 16 17
"12/01/2007" 12 19 10
Is this query possible. The RowID is the indicator as to which rows the values belong to.
Regards,
Ready1
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
i still get an exception. this is my full code just retrieving the date for now
SELECT DISTINCT t1.RowID,
(SELECT
newValue =
CASE
WHEN vx.MyDataTypeID = 2 THEN CONVERT(INT, t2.Val)
WHEN vx.MyDataTypeID = 3 THEN CONVERT(decimal(6,4), t2.Val)
WHEN vx.MyDataTypeID = 6 THEN CONVERT(DATETIME, t2.Val, 101)
ELSE
CAST(t2.Val AS VARCHAR(50))
END
FROM MyData t2 INNER JOIN MyDataCol vx ON t2.MyDataColumnID = vx.ID WHERE t2.RowID = t1.RowID AND vx.Name = 'Draw') AS DrawDate
FROM MyData t1
ORDER BY t1.RowID
regards,
Ready1
Hmmm... can't combine different datatypes in the same column. Try this:
SELECT t1.RowID,
MAX(CASE WHEN vx.MyDataTypeID = 2 THEN CONVERT(INT, t2.Val) END) VAL2,
MAX(CASE WHEN vx.MyDataTypeID = 3 THEN CONVERT(decimal(6,4), t2.Val) END) VAL3,
MAX(CASE WHEN vx.MyDataTypeID = 6 THEN CONVERT(DATETIME, t2.Val, 101 END DATE
FROM MyData t1 INNER JOIN MyDataCol vx ON t2.MyDataColumnID = vx.ID Where vx.Name = 'Draw'
Group By t1.RowID
ORDER BY t1.RowID
This return all rows in MyData and MyDataCol which are of type 6. Changing your query to only select RowID and Val gives me the 2 date Val.
Select RowID, Val
FROM VertXData t1 INNER JOIN VertXDataCol vx ON t1.VertXDataColumnID = vx.ID Where vx.Name = 'Draw'
and vx.VertXdatatypeID=6
and Isdate(t1.val) = 0
The dates are:
RowID Val
1 17/08/2007 10:00:15 AM
2 25/08/2007 10:00:15 AM
Regards,
Ready1
One thing--I see we are using the wrong date format. Also, I'm confused where does the 10:00:15 AM come from? Try this:
SELECT t1.RowID,
MAX(CASE WHEN vx.MyDataTypeID = 2 THEN CONVERT(INT, t1.Val) END) VAL2,
MAX(CASE WHEN vx.MyDataTypeID = 3 THEN CONVERT(decimal(6,4), t1.Val) END) VAL3,
MAX(CASE WHEN vx.MyDataTypeID = 6 THEN CONVERT(DATETIME, t1.Val, 103 END DATE
FROM MyData t1 INNER JOIN MyDataCol vx ON t1.MyDataColumnID = vx.ID Where vx.Name = 'Draw'
Group By t1.RowID
ORDER BY t1.RowID
Business Accounts
Answer for Membership
by: matthewspatrickPosted on 2007-08-16 at 18:26:38ID: 19714089
SELECT DISTINCT t1.RowID,
(SELECT t2.Value FROM YourTable t2 WHERE t2.RowID = t1.RowID AND Type = 'Date') AS Date,
(SELECT t2.Value FROM YourTable t2 WHERE t2.RowID = t1.RowID AND Type = 'Val1') AS Val1,
(SELECT t2.Value FROM YourTable t2 WHERE t2.RowID = t1.RowID AND Type = 'Val2') AS Val2,
(SELECT t2.Value FROM YourTable t2 WHERE t2.RowID = t1.RowID AND Type = 'Val3') AS Val3
FROM YourTable t1
ORDER BY t1.RowID