Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SELECT TOP 1000 [Value]                       
FROM [OnlineQnres].[dbo].[tmp_DataSets]
WHERE [VariableID] in ('1')
UNION ALL
SELECT TOP 1000 [Value]
FROM [OnlineQnres].[dbo].[tmp_oDataSets]
WHERE [VariableID] in ('4')

The above query produces 2000 rows; 1000 with Value for VariableID = 1, and 1000 with Value for VariableID = 2. What I want is 1000 rows, with one column (val1) containing the values for VariableID = 1, and the other column (val2) with the values for VariableID = 2.

share|improve this question
2  
Before anyone attempts to answer, can you tell us what ORDER BY should be used for the 2 subqueries? Without this, your query may return arbitrary results (any 1000 rows.) –  ypercube Aug 20 '13 at 23:51
    
Hello, right now I just wnated to see if thats possible or not. So i wanted to to just select any top 1000 rows to just see the results in crosstab. we can order by variableID. –  user2701287 Aug 21 '13 at 0:07
    
Sure it's possible, but you need to describe which row that has variableID = 1 goes with which row that has variableID = 4. –  Aaron Bertrand Aug 21 '13 at 0:56
add comment

1 Answer

;WITH x AS 
(
  SELECT TOP (1000) Value, row_num = ROW_NUMBER() OVER (ORDER BY Value)
    FROM OnlineQnres.dbo.tmp_oDataSets
    WHERE VariableID = '1'
), y AS
(
  SELECT TOP (1000) Value, row_num = ROW_NUMBER() OVER (ORDER BY Value)
    FROM OnlineQnres.dbo.tmp_oDataSets
    WHERE VariableID = '4'
)
SELECT val1 = x.Value, val2 = y.Value
FROM x INNER JOIN y
ON x.row_num = y.row_num;

As I stated in a comment, for this correlation to be meaningful, you need to define how the ROW_NUMBER() is calculated (or indicate how these two sets of values are related). In this case it will correlate the smallest value for variableID = 1 with the smallest value for variableID = 2, the 2nd smallest value for variableID = 1 with the 2nd smallest value for variableID = 2, etc.

Also, as others noted, to get predictable results, you need to define TOP (1000) with a separate ORDER BY.

Essentially, if you have these rows in the table, and pretend you are only looking for two of each variableID:

VariableID    Value
----------    -------
1             foo
1             bar
4             mort
4             splunge

What does the result look like? Should it be:

val1    val2
------  -------
foo     mort
bar     splunge

If so, why? If not, why not? Or should it be:

val1    val2
------  -------
foo     splunge
bar     mort

If so, why? If not, why not? Or should it be:

val1    val2
------  -------
foo     NULL
bar     NULL
NULL    mort
NULL    splunge

Or something else entirely?

There are other query approaches too, but they depend on your requirements, and how the rows are linked other than the fact that they are "any" row that match the WHERE clauses.

Flimsy requirements lead to flimsy answers, sorry.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.