How to get this?

Question

Post reply

How to get this?

parthi-1705

SSCrazy Eights

Points: 9277
More actions
January 22, 2010 at 12:41 am

#386159

DECLARE @Person TABLE (Status INT,Name Varchar(20))
DECLARE @NumberTbl TABLE (Numbers INT,Status INT)
INSERT INTO @NumberTbl
SELECT 1,2 UNION
SELECT 2,5 UNION
SELECT 3,3 UNION
SELECT 4,0
Select * from @NumberTbl
INSERT INTO @Person
SELECT 1,'ONE' UNION
SELECT 2,'TWO' UNION
SELECT 3,'THREE' UNION
SELECT 4,'FOUR' UNION
SELECT 5,'FIVE'
-- Select * from @Person
--- I Need
Select 1,'THREE,FOUR,FIVE'-- Remaining Things
union
Select 2,'ONE,TWO,THREE,FOUR,FIVE'
union
Select 3,'FOUR,FIVE'
union
Select 4,'ALL'
-- OR
Select 1,'THREE','FOUR','FIVE'-- Remaining Things
Select 2,'ONE','TWO','THREE','FOUR','FIVE'
Select 3,'FOUR','FIVE'
Select 4,'ALL'
Thanks
Parthi
Thanks
Parthi

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

Is this some kind of puzzle 🙂 ?? Care to elaborate a bit?

Can you tell us what do you mean by 'remaining things'?

---------------------------------------------------------------------------------

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

DECLARE @Person TABLE (Status INT,Name Varchar(20))

DECLARE @NumberTbl TABLE (Numbers INT,Status INT)

INSERT INTO @NumberTbl

SELECT 1,2 UNION

SELECT 2,5 UNION

SELECT 3,3 UNION

SELECT 4,0

Select * from @NumberTbl

INSERT INTO @Person

SELECT 1,'ONE' UNION

SELECT 2,'TWO' UNION

SELECT 3,'THREE' UNION

SELECT 4,'FOUR' UNION

SELECT 5,'FIVE'

-- Select * from @Person

Two Tables are there With data

Remaining Things :

Here it means until for Number 1 Status is 2 so remaining Things are THREE,FOUR,FIVE

like wise i need

--- I Need

Select 1,'THREE,FOUR,FIVE'-- Remaining Things

union

Select 2,'ALL'

union

Select 3,'FOUR,FIVE'

union

Select 4,'ONE,TWO,THREE,FOUR,FIVE'

-- OR

Select 1,'THREE','FOUR','FIVE'-- Remaining Things

Select 2,'ALL'

Select 3,'FOUR','FIVE'

Select 4,'ONE','TWO','THREE','FOUR','FIVE'

Thanks

Parthi

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Is this homework? If not, what are you trying to get here? I don't understand the reason.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

NO it is not like Home work or some thing else i just hiding my business logic and giving you a simple thing so that you can understand

Thanks
Parthi

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

I think this gives you what you want, but I don't fully understand the requirements

SELECT n.Numbers,

COALESCE(STUFF((SELECT ','+p.Name AS "text()"

FROM @Person p

WHERE p.Status > n.Status

ORDER BY p.Status

FOR XML PATH('')),1,1,''),'ALL') AS [Remaining Things]

FROM @NumberTbl n

ORDER BY n.Numbers

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

Hi Mark

Nice one, that is good in 2005,we are using 2000 how to get in 2000 any idea.

The above O/P is what i need

Thanks

Parthi

Thanks
Parthi

Nabha SSCrazy Eights Points: 8534 More actions · Answer 7

Not sure if there is a better way to do it in 2000 , but if you have the luxury of creating a function, you can do it this way...

CREATE FUNCTION GETNAMES(@status int)

RETURNS varchar(100)

AS

BEGIN

DECLARE @NAME varchar(100)

SET @NAME = ''

SELECT @NAME = @NAME + ',' + NAME FROM Person

WHERE STATUS > @status

RETURN (STUFF(@NAME, 1, 1, ''))

END

SELECT n.Numbers,COALESCE(dbo.Getnames(n.status), 'ALL')

FROM #NumberTbl n

ORDER BY n.Numbers

BTW, its a 2005 forum, you should've put this in 2000

---------------------------------------------------------------------------------

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

parthi-1705 (1/22/2010)
Hi Mark
Nice one, that is good in 2005,we are using 2000 how to get in 2000 any idea.
...

Ah. Then you should have used one of the SQL 2000 forums instead of this SQL 2005 forum.

Further helps to understanding and getting better answers faster would be 1) using the [ code ] tags and 2) asking a straightforward question. please see here[/url] for more explanation.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 9

Hi Friends

DECLARE @Tmp TABLE (I INT ,VALUE VARCHAR(25))

INSERT INTO @Tmp

SELECT 00,'1,2,3,4,5'

UNION ALL SELECT 01,'1,2,3,4,5'

UNION ALL SELECT 02,'1,2,3,4,5'

UNION ALL SELECT 03,'1,2,3,4,5'

UNION ALL SELECT 04,'1,2,3,4,5'

UNION ALL SELECT 05,'2,3,4,5'

UNION ALL SELECT 06,'2,3,4,5'

UNION ALL SELECT 07,'2,3,4,5'

UNION ALL SELECT 08,'1,2,3,4,5'

UNION ALL SELECT 09,'1,2,3,4,5'

UNION ALL SELECT 10,'1,2,3,4,5'

UNION ALL SELECT 11,'1,2,3,4,5'

UNION ALL SELECT 12,'3,4,5'

UNION ALL SELECT 13,'1,2,3,4,5'

UNION ALL SELECT 14,'1,2,3,4,5'

UNION ALL SELECT 15,'2,3,4,5'

UNION ALL SELECT 16,'1,2,3,4,5'

UNION ALL SELECT 17,'1,2,3,4,5'

UNION ALL SELECT 18,'1,2,3,4,5'

UNION ALL SELECT 19,'2,3,4,5'

UNION ALL SELECT 20,'2,3,4,5'

UNION ALL SELECT 21,'1,2,3,4,5'

UNION ALL SELECT 22,'2,3,4,5'

UNION ALL SELECT 23,'1,2,3,4,5'

UNION ALL SELECT 24,'1,2,3,4,5'

UNION ALL SELECT 25,'1,2,3,4,5'

UNION ALL SELECT 26,'1,2,3,4,5'

UNION ALL SELECT 27,'1,2,3,4,5'

UNION ALL SELECT 28,'1,2,3,4,5'

UNION ALL SELECT 29,'2,3,4,5'

UNION ALL SELECT 30,'1,2,3,4,5'

UNION ALL SELECT 31,'1,2,3,4,5'

UNION ALL SELECT 32,'1,2,3,4,5'

UNION ALL SELECT 33,'1,2,3,4,5'

UNION ALL SELECT 34,'1,2,3,4,5'

UNION ALL SELECT 35,'1,2,3,4,5'

UNION ALL SELECT 36,'1,2,3,4,5'

UNION ALL SELECT 37,'1,2,3,4,5'

UNION ALL SELECT 38,'1,2,3,4,5'

UNION ALL SELECT 39,'1,2,3,4,5'

UNION ALL SELECT 40,'3,4,5'

UNION ALL SELECT 41,'4,5'

UNION ALL SELECT 42,'4,5'

UNION ALL SELECT 43,'4,5'

SELECT * FROM @Tmp

I Need like this any idea

SELECT 01,04, '1,2,3,4,5'

SELECT 05,07, '2,3,4,5'

SELECT 08,11, '1,2,3,4,5'

SELECT 12,12, '3,4,5'

SELECT 13,14, '1,2,3,4,5'

SELECT 15,15, '2,3,4,5'

SELECT 16,18, '1,2,3,4,5'

SELECT 19,20, '2,3,4,5'

SELECT 21,21, '1,2,3,4,5'

SELECT 22,22, '2,3,4,5'

SELECT 23,28, '1,2,3,4,5'

SELECT 29,29, '2,3,4,5'

SELECT 30,39, '1,2,3,4,5'

SELECT 40,40, '3,4,5'

SELECT 41,43, '4,5'

Thanks

Parthi

Thanks
Parthi

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 10

WITH CTE AS (

SELECT I,VALUE,

ROW_NUMBER() OVER(ORDER BY I)-

ROW_NUMBER() OVER(PARTITION BY VALUE ORDER BY I) AS rnDiff

FROM @Tmp)

SELECT MIN(I) AS FromI,

MAX(I) AS ToI,

VALUE

FROM CTE

GROUP BY VALUE,rnDiff

ORDER BY MIN(I);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537