What syntax is similar to UNION ALL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

What syntax is similar to UNION ALL

Author

Topic

pineappleflower
Starting Member

6 Posts

Posted - 2009-01-05 : 05:27:29

I want to write a query where 1 record will have 2 data lines. For example,

select categoryname, 1 as 'a' from categories union all select categoryname, 2 as 'a' from categories order by categoryname

But my own browser does not accept this UNION syntax, is there any other syntax that i can used to retrieve the same data?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-05 : 05:30:50

can u post some output data

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 05:33:09

Your own browser?
What do you mean?

The functional equivalent to

quote:

select		categoryname,
		1 as 'a'
from		categories

union all

select		categoryname,
		2 as 'a'
from		categories

order by	categoryname

would be

SELECT		c.CategoryName,
		w.a
FROM		Categories AS c
CROSS JOIN	(
			SELECT	1 AS a

			UNION ALL

			SELECT	2
		) AS w
ORDER BY	c.CategoryName

but it still involves UNION ALL
Another try might be

SELECT		c.CategoryName,
		v.Number AS a
FROM		Categories AS c
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number BETWEEN 1 AND 2
ORDER BY	c.CategoryName

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 05:34:43

whats error you got?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 05:42:56

Maybe it's a homework question?

E 12°55'05.63"
N 56°04'39.26"

pineappleflower
Starting Member

6 Posts

Posted - 2009-01-05 : 05:50:57

My company have own unique of browser, i get the error 'Method of 'Refresh' of object 'IProxyEDLBrowser' failed.

Example, i want data look likes:
Water, 1
Water, 2
Drink, 1
Drink, 2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 05:57:52

Did you have a look at this suggestion?

SELECT		c.CategoryName,
		v.Number AS a
FROM		Categories AS c
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number BETWEEN 1 AND 2
ORDER BY	c.CategoryName

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources