August 13, 2015 at 7:55 am
Hello,
I would like to do a query that would give me the info twice, with just a small difference. Let me give you a small example to explain.
Let's say I have a table with elements A, B and C. Whatever it is, it does not matter.
I would like to get all the data so I just do that:
SELECT A, B, C
Then I would like to add a bit of text, so I'll add:
SELECT A, B, C, 'Text1' as D
The thing is that I would like every single elements (A,B,C) to be repeated twice: Once with Text1, once with Text2.
Let's say my data looks like that:
1 2 3
2 1 3
4 1 2
Then my query would give me this:
1 2 3 Text1
2 1 3 Text1
4 1 2 Text1
1 2 3 Text2
2 1 3 Text2
4 1 2 Text2
(in no particular order)
Is there a way I can do this in just one query?
Thank you.
August 13, 2015 at 7:59 am
Sure here are a couple of examples.
select *
from MyTable
cross join (select 'Text1' as MyText union all select 'Text2') x
select *
from MyTable
cross apply (values('Text1'), ('Text2')) x (MyText)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2015 at 8:48 am
Neither of those seemed to work, sadly.
The first one tells me I lack a "FROM" clause (and underlines union to identify where's the mistake)
The second one says it lacks a key word (or i'm not sure how you say that in english but there's a clause missing) and it underlines apply
Any idea why?
August 13, 2015 at 8:56 am
Another way of doing it:
SELECT A, B, C, 'Text 1' MyText
FROM MyTable
UNION ALL
SELECT A, B, C, 'Text 2'
FROM MyTable
John
August 13, 2015 at 8:59 am
This works...
SELECT A, B, C, txt
FROM
(SELECT 1 AS A, 2 AS B, 3 AS C
UNION ALL
SELECT 2,1,3
UNION ALL
SELECT 4,1,2) x
CROSS JOIN
(SELECT 'Text1' AS txt
UNION ALL
SELECT 'Text2') y
In your case, it might look more like
SELECT t1.A, t1.B, t1.C, t2.txt
FROM table1 t1 CROSS JOIN table2 t2
August 13, 2015 at 9:04 am
jodevil99 (8/13/2015)
Neither of those seemed to work, sadly.The first one tells me I lack a "FROM" clause (and underlines union to identify where's the mistake)
The second one says it lacks a key word (or i'm not sure how you say that in english but there's a clause missing) and it underlines apply
Any idea why?
Those both work perfectly on my system. What is the actual code you are using?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2015 at 9:07 am
jodevil99 (8/13/2015)
Neither of those seemed to work, sadly.The first one tells me I lack a "FROM" clause (and underlines union to identify where's the mistake)
The second one says it lacks a key word (or i'm not sure how you say that in english but there's a clause missing) and it underlines apply
Any idea why?
This really doesn't help us help you.
Please post the code that is giving you the errors and post the complete text of the error messages.
August 13, 2015 at 9:08 am
John Mitchell-245523 (8/13/2015)
Another way of doing it:
SELECT A, B, C, 'Text 1' MyTextFROM MyTable
UNION ALL
SELECT A, B, C, 'Text 2'
FROM MyTable
John
Thank you John, that worked very well.
Thank you every one.
August 13, 2015 at 9:16 am
jodevil99 (8/13/2015)
Neither of those seemed to work, sadly.The first one tells me I lack a "FROM" clause (and underlines union to identify where's the mistake)
The second one says it lacks a key word (or i'm not sure how you say that in english but there's a clause missing) and it underlines apply
Any idea why?
Both of these work for me:
DECLARE @MyTable TABLE (Col1 int, Col2 int, Col3 int);
INSERT INTO @MyTable (Col1, Col2, Col3)
VALUES (1,2,3), (2,1,3), (4,1,2);
SELECT Col1, Col2, Col3, Col4
FROM @MyTable
CROSS APPLY (VALUES ('Text1'), ('Text2')) dt(Col4)
SELECT Col1, Col2, Col3, Col4
FROM @MyTable
CROSS JOIN (SELECT 'Text1' UNION ALL SELECT 'Text2') dt(Col4)
Results for each:
Col1 Col2 Col3 Col4
----------- ----------- ----------- -----
1 2 3 Text1
1 2 3 Text2
2 1 3 Text1
2 1 3 Text2
4 1 2 Text1
4 1 2 Text2
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 13, 2015 at 9:25 am
jodevil99 (8/13/2015)
John Mitchell-245523 (8/13/2015)
Another way of doing it:
SELECT A, B, C, 'Text 1' MyTextFROM MyTable
UNION ALL
SELECT A, B, C, 'Text 2'
FROM MyTable
John
Thank you John, that worked very well.
Thank you every one.
Glad you got a working solution, but it would be nice to figure out why the other solutions failed for you.
August 13, 2015 at 10:07 am
Lynn Pettis (8/13/2015)
jodevil99 (8/13/2015)
John Mitchell-245523 (8/13/2015)
Another way of doing it:
SELECT A, B, C, 'Text 1' MyTextFROM MyTable
UNION ALL
SELECT A, B, C, 'Text 2'
FROM MyTable
John
Thank you John, that worked very well.
Thank you every one.
Glad you got a working solution, but it would be nice to figure out why the other solutions failed for you.
Especially since this way is scanning the table twice, while the other ways only does it once.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 13, 2015 at 10:18 am
Well, considering the error messages did not say much more and were in french, I don't think it was really relevent to post a french error message here
As for my code, here's a slightly modified version of what I made work:
With quer as (Some other query)
select a.stuff1,
a.stuff2,
a.stuff3,
a.stuff4,
a.stuff5,
a.stuff6,
a.stuff7,
a.stuff8,
a.stuff9,
'Text1' as Mytext,
a.stuff10,
a.stuff11
from quer a
UNION ALL
select b.stuff1,
b.stuff2,
b.stuff3,
b.stuff4,
b.stuff5,
b.stuff6,
b.stuff7,
b.stuff8,
-b.stuff9,
'text2',
b.stuff10,
b.stuff11
FROM quer b
August 13, 2015 at 10:25 am
jodevil99 (8/13/2015)
Well, considering the error messages did not say much more and were in french, I don't think it was really relevent to post a french error message hereAs for my code, here's a slightly modified version of what I made work:
With quer as (Some other query)
select a.stuff1,
a.stuff2,
a.stuff3,
a.stuff4,
a.stuff5,
a.stuff6,
a.stuff7,
a.stuff8,
a.stuff9,
'Text1' as Mytext,
a.stuff10,
a.stuff11
from quer a
UNION ALL
select b.stuff1,
b.stuff2,
b.stuff3,
b.stuff4,
b.stuff5,
b.stuff6,
b.stuff7,
b.stuff8,
-b.stuff9,
'text2',
b.stuff10,
b.stuff11
FROM quer b
Didn't ask to see what you got to work, I asked to see the code you could NOT get to work and the complete error messages when you run that code.
August 13, 2015 at 10:28 am
jodevil99 (8/13/2015)
Well, considering the error messages did not say much more and were in french, I don't think it was really relevent to post a french error message here
Actually it would be fine especially if you include the error number along with the code. We can look up the error message by error number and see what the message is for any language. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2015 at 10:38 am
FROM keyword not found where expected
select *
from quer a
cross join (select 'Text1' as MyText union all select 'Text2') x
missing keyword
select *
from quer a
cross apply (values('Text1'), ('Text2')) x (MyText)
SQL command not properly ended
select *
from quer
cross apply (values('Text1'), ('Text2')) x (MyText)
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply