1

I know this has been asked before, but the solutions given did not work for me unfortunately.

I have several queries (they will be 42 in total, but let's try with 2 for this example) looking into one Table and returning results with different conditions. How can I simply put the results in adjacent columns with SQL?

The queries are:

SELECT Column5 as Alias1 FROM Table WHERE Column2 = 1 AND Column3 = 1 AND Column4 =1

SELECT Column5 as Alias2 FROM Table WHERE Column2 = 1 AND Column3 = 1 AND Column4 =2

... (all combinations of values in Columns 2, 3 and 4 which happen to be 42)

SELECT Column5 as Alias42 FROM Table WHERE Column2 = 7 AND Column3 = 3 AND Column4 =3

Each of the above queries works as expected and returns one column with 44 lines. All I want to do is have the queries return the results in side by side columns (so I need 42 columns with 44 lines each).

Any ideas?

I have tried the following:

Based on this: How do i combine multiple select statements in separate columns?

SELECT TMP1.Alias1,TMP2.Alias2 FROM 
(SELECT Column5 as Alias1 FROM Table WHERE Column2 = 1 AND Column3 = 1  AND Column4 =1) AS TMP1,
(SELECT Column5 as Alias2 FROM Table WHERE Column2 = 1 AND Column3 = 1  AND Column4 =2) AS TMP2

This returns 44*44 lines instead of 44.

Based on this: Merge result of two sql queries in two columns

SELECT q1.Alias1, q2.Alias2
FROM (
(SELECT Column5 as Alias1 FROM Table WHERE Column2 = 1 AND Column3 = 1  AND Column4 =1) q1)
JOIN
(SELECT Column5 as Alias2 FROM Table WHERE Column2 = 1 AND Column3 = 1  AND Column4 =2) q1) q2
ON q1.Alias1 = q2.Alias2

Doesn't work, since I don't want to join the tables with any conditions, I just want to have the results next to each other. Also, doesn't compile.

Similar to the above (suggested from a friend):

SELECT Table1.Column5, Table2.Column5
FROM Table AS Table1,
Table AS Table2
WHERE Column2 = 1 AND Column3 = 1  AND Column4 =1
AND   Column2 = 1 AND Column3 = 1  AND Column4 =2

Doesn't work, since it returns 44*44 instead of 44 lines (it's unnecessarily joining tables).

Also this: How Do I Combine Multiple SQL Queries? is a combination of the above.

To give some context, I'm trying to reformat a set of data in Excel from a long form to a wide form so as to perform statistical tests on them. So I am kind of limited by the Excel SQL functionality (Access syntax).

Any help will be greatly appreciated.

EDIT:

I am not posting this as an answer, since it's not solving my problem fully with SQL, but it is solving my problem.

I used Jim Sosa's solution and modified it and I have:

select
   iif([Column2]=1 AND [Column3]=1 AND [Column4]=1,Column5,null) as column1,
   iif([Column2]=1 AND [Column3]=1 AND [Column4]=2,Column5,null) as column2
... (40 more iffs)
from Table

Then I get what I want, but with extra nulls. I then get rid of those nulls, like so: http://exceltactics.com/automatically-delete-blank-cells-organize-data/

and that's it.

Thank you for all the responses.

I appreciate your comments that this is not a typical SQL problem :)

Cheers

Improve this question
13
  • Does the order of the results in a specific column and their relation to the other results and rows matter? and a pivot table doesn't work because.... Commented Feb 25, 2015 at 16:40
  • Would the second answer works here? stackoverflow.com/questions/13065105/… Commented Feb 25, 2015 at 16:40
  • You mentioned 42 combinations, but then you also mentioned 44 rows. Is it 42 or 44? Where do the additional 2 rows come from? Commented Feb 25, 2015 at 16:41
  • @xQbert unfortunately it does, since they represent data from a specific person. So it needs to be in that order. Commented Feb 25, 2015 at 16:42
  • @dasblikenlight It's 42 columns and 44 rows (I don't need to worry about the rows, this is what I will get if I get it right, but the columns I need to assemble next to each other) Commented Feb 25, 2015 at 16:43

5 Answers 5

Reset to default
1

Your second solution, which is based on joining, comes very close. You need to change it to join on an artificial row number, like this:

(SELECT
    (SELECT COUNT(*) FROM Table t WHERE t.Id < tt.Id) AS RowNum
,   Column5 as Alias1
FROM Table tt
WHERE Column2 = 1 AND Column3 = 1  AND Column4 =1) AS q1

    JOIN

(SELECT
    (SELECT COUNT(*) FROM Table q WHERE q.Id < qq.Id) AS RowNum
,   Column5 as Alias2
FROM Table qq
WHERE Column2 = 1 AND Column3 = 1  AND Column4 =1) AS q2

    ON q1.RowNum = q2.RowNum

The (SELECT COUNT(*) FROM Table q WHERE q.Id < qq.Id) as RowNum trick assigns each row of each select an artificial RowNum. Your tables must have a unique ID column in order for this trick to work.

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

3 Comments

Hi @dasblinkenlight , where does this part come from ) q1) q2 e.g after Column4 =2) ?
Thanks! Seems to be getting there, although always getting a "Syntax error in FROM clause" with JOINS :(
@menackin The fixed-up syntax worked with mysql, but I am not sure if it is going to work with ms-access. Give it a try.
1

Quit making it hard on yourself! :P SQL isn't always the answer. I'm hitting myself right now because I try to use it to solve the data woes I run into as well... but in this case, and especially when dealing with dynamic number of columns; Excel pivot tables work great in this manner...

In my opinion this is nothing more than a concatenation of the "Categories" followed by a pivot table.

Since the column 2,3,4 are just a category denoting a specific response for a a user... create a pseudo column to pivot upon that category and pivot as below.

I used a '.' to separate out the values in case we get into multi-digit values. you can parse it out later if needed.

in Col F all I did was concatenate columns b,c,d to give me a unique value as a column header which would be the same for each user (col1)

I then pivoted on the data... voila.

enter image description here

Each category gets its own column per response/user and each user has its own row and now the row header actually ties to the category without having to go lookup what you did in the SQL...

As more combinations exist, more columns are added. Users lacking data for specific categories, simply get a blank in that row/column intersection. Such as 10 and category 3.1.1.

Remove the grand totals, or change how they relate if needed... Min/Max, Sum... whatever.

The only gotcha I can forsee is if col2, col3, col4 don't actually have the same values.... (like an extra space or something on some...) but as the values are numeric and you're trying to use case statements to filter by them... I think this will work...

Improve this answer

Comments

0

I haven't used access in a long time but I believe there's a couple ways to do this. Though one of the more entertaining would be this:

select Max(iif(Column2 = 1 AND Column3 = 1 AND Column4 =1, column5, 0)) as column1,
       Max(iif(Column2 = 1 AND Column3 = 1 AND Column4 =2, column5, 0)) as column2,
       ...
       Max(iif(Column2 = 7 AND Column3 = 3 AND Column4 =4, column5, 0)) as column42
  from table

I am assuming here that column5 is a positive number though it may work even if it's a string. If it doesn't you may have to change the 0 to an empty string or some such. The aggregate functions will ensure you only get one row back. You could also try multiple sub queries in your select clause, but I'm not sure that access even supports that.

Improve this answer

4 Comments

This looks really elegant, but didn't work. It says there is some "reserved word" in the Column3 clause, trying to see how this might be fixed. Thanks.
It should work as I've used something similar before. Try breaking it down to just a couple columns until you can trace what reserve word it's talking about. You can always put brackets [table] on reserve words
Almost there, although I think I'm missing something. The one you mention only gives me the max value, which is not what I want, since I need all the values except 0. If I remove the max part and substitute 0 with null, then I almost have what I want, only I need to get rid of the nulls...
Sorry @menackin, I haven't been near a computer in 6 days dealing with a family emergency. Anyway, you need an aggregate functions to get one row back, not all the empty null records you must be getting back. The max will only return the value with the largest value. If your column5 always has a positive value then it is the one that gets returned (the max of 0, 0, 0, column5, 0, 0, 0... is always column5). Hope that makes sense. If your solution now works I guess it doesn't matter, but I would think you'd be getting multiple rows back.
0

You mention repeatedly that you don't want joins - I assume that means that actually, there is no relationship between the data you want in column 1 and column 2 of your result.

The happy fact that you now get 44 rows for every separate query does not mean this is always the case, and there is no way that you seem to be able to ensure that row number 26 of query1 has any relation to row number 26 of query2.

If you cannot define such a relationship, SQL cannot do it either! SQL is made to return rows of data in which the columns all are somehow related to each other. If you cannot give a relationship, no SQL engine can make up that relationship.

If somehow, you are convinced that by accident ever nth row in the 44 queries relates to every other nth row, just because you assume the data gets returned in a meaningful way, just handle your presentation where it belongs. And that is not in SQL!

NOTE: If you do have a way of defining the relationship between the individual rows, you should indicate that in your queries. You could then write a(n ugly) query to combine everything - but it is still more likely that this would be much better handled by your application!

Improve this answer

1 Comment

Thanks for the response. I know that I will always get 44 rows, since I have a known number of combinations that I have controlled in my data.
0

I doubt this works in Access but it would return the results you seek...

with T as (
    select
        column2, column3, column4, column5,
        row_number() over (
            partition by column2, column3, column4
            order by column5 /* ?? or possibly (select 1) for a random ordering of rows */
        ) as rownum
    from <yourtable>
)
select
    min(case when column2 = 1 and column3 = 1 and column4 = 1 then column5 end),
    min(case when column2 = 1 and column3 = 1 and column4 = 2 then column5 end),
    min(case when column2 = 1 and column3 = 1 and column4 = 3 then column5 end),
    min(case when column2 = 1 and column3 = 2 and column4 = 1 then column5 end),
    min(case when column2 = 1 and column3 = 2 and column4 = 2 then column5 end),
    min(case when column2 = 1 and column3 = 2 and column4 = 3 then column5 end),
    min(case when column2 = 1 and column3 = 3 and column4 = 1 then column5 end),
    min(case when column2 = 1 and column3 = 3 and column4 = 2 then column5 end),
    min(case when column2 = 1 and column3 = 3 and column4 = 3 then column5 end),
    ...
    min(case when column2 = 7 and column3 = 1 and column4 = 1 then column5 end),
    min(case when column2 = 7 and column3 = 1 and column4 = 2 then column5 end),
    min(case when column2 = 7 and column3 = 1 and column4 = 3 then column5 end),
    min(case when column2 = 7 and column3 = 2 and column4 = 1 then column5 end),
    min(case when column2 = 7 and column3 = 2 and column4 = 2 then column5 end),
    min(case when column2 = 7 and column3 = 2 and column4 = 3 then column5 end),
    min(case when column2 = 7 and column3 = 3 and column4 = 1 then column5 end),
    min(case when column2 = 7 and column3 = 3 and column4 = 2 then column5 end),
    min(case when column2 = 7 and column3 = 3 and column4 = 3 then column5 end)
from T
group by rownum

Perhaps Access likes this better??

select
    min(case when column2 = 1 and column3 = 1 and column4 = 1 then column5 end),
    min(case when column2 = 1 and column3 = 1 and column4 = 2 then column5 end),
    min(case when column2 = 1 and column3 = 1 and column4 = 3 then column5 end),
    min(case when column2 = 1 and column3 = 2 and column4 = 1 then column5 end),
    min(case when column2 = 1 and column3 = 2 and column4 = 2 then column5 end),
    min(case when column2 = 1 and column3 = 2 and column4 = 3 then column5 end),
    min(case when column2 = 1 and column3 = 3 and column4 = 1 then column5 end),
    min(case when column2 = 1 and column3 = 3 and column4 = 2 then column5 end),
    min(case when column2 = 1 and column3 = 3 and column4 = 3 then column5 end),
    ...
    min(case when column2 = 7 and column3 = 1 and column4 = 1 then column5 end),
    min(case when column2 = 7 and column3 = 1 and column4 = 2 then column5 end),
    min(case when column2 = 7 and column3 = 1 and column4 = 3 then column5 end),
    min(case when column2 = 7 and column3 = 2 and column4 = 1 then column5 end),
    min(case when column2 = 7 and column3 = 2 and column4 = 2 then column5 end),
    min(case when column2 = 7 and column3 = 2 and column4 = 3 then column5 end),
    min(case when column2 = 7 and column3 = 3 and column4 = 1 then column5 end),
    min(case when column2 = 7 and column3 = 3 and column4 = 2 then column5 end),
    min(case when column2 = 7 and column3 = 3 and column4 = 3 then column5 end)
from
    (
    select
        column2, column3, column4, column5,
        (
        select count(*)
        from <yourtable> as t2
        where t2.column2 = t1.column2 and t2.column3 = t2.column3 and t2.column4 = t1.column4
            and t2.column5 < t1.column5 /* need some way to break ties */
        ) as rownum
    from <yourtable> as t1
    ) as T
group by rownum

Without any description of the values in column5 (or column1 for that matter) it's hard to speculate what might work for you.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.