A Query That I Can't Explain - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

A Query That I Can't Explain

Author

Topic

LacOniC
Starting Member

29 Posts

Posted - 2013-12-10 : 10:20:17

I need a query that i can't explain in one sentence due to my English.

SELECT Column1, Colum2 FROM Table1

Column1 is ID, Colum 2 is Char and Colum2 contains only two value as "E" and "K".

I need a result like:

Column1 Column2
112431 E
112433 K
112234 E
112223 K
112567 E
112387 K

U see Column2. Every row should return different value.

Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-10 : 10:40:47

[code]
select Column1, Column2 = case when row_no % 2 = 0 then 'K' else 'E' end
from
(
select Column1, row_no = row_number() over (order by Column1)
from Table1
) d
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-10 : 12:20:39

LacOniC's sample output was clearly not ordered by Column1. To keep the existing "sequence" of the table rows (which could theoretically vary from run to run if ORDER BY is not used, but often does not), you can do this:



SELECT IDENTITY(int, 1, 1) AS id, *
INTO #table1
FROM table1

SELECT Column1, CASE WHEN id % 2 = 1 THEN 'E' ELSE 'K' END AS Column2
FROM #table1
ORDER BY id

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-10 : 12:25:00

For example:


USE tempdb

IF OBJECT_ID('tempdb..#table1') IS NOT NULL
    DROP TABLE #table1

CREATE TABLE table1 (
    Column1 varchar(30)
    )
INSERT INTO table1
SELECT '112431' 
UNION ALL SELECT '112433'
UNION ALL SELECT '112234'
UNION ALL SELECT '112223'
UNION ALL SELECT '112567'
UNION ALL SELECT '112387'
GO

SELECT IDENTITY(int, 1, 1) AS id, *
INTO #table1
FROM table1

SELECT Column1, CASE WHEN id % 2 = 1 THEN 'E' ELSE 'K' END AS Column2
FROM #table1
ORDER BY id

select Column1, Column2 = case when row_no % 2 = 0 then 'K' else 'E' end
from
(
select Column1, row_no = row_number() over (order by Column1)
from   Table1
) d

GO

DROP TABLE table1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-10 : 12:28:32

The original table contains the values E and K. So, I'd expect that the query needs to take that into account and not generate the E and K.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-10 : 12:36:48

Maybe this?

DECLARE @Foo TABLE(Column1 INT, Column2 CHAR(1))

INSERT @Foo
VALUES
(112431, 'E'),
(112433, 'K'),
(112234, 'E'),
(112223, 'K'),
(112567, 'E'),
(112387, 'K')


SELECT
	Column1,
	Column2
FROM
	(
		SELECT 
			*,
			ROW_NUMBER() OVER (PARTITION BY Column2 ORDER BY Column1) AS RowNum
		FROM
			@Foo
	) AS A
ORDER BY
	RowNum,
	Column2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources