How to handle this

Question

Post reply

How to handle this

priestxandar

Right there with Babe

Points: 724
More actions
April 17, 2014 at 7:03 am

#306714

I need a way how to handle described situation bellow, which will work very fast for lot of rows.
-- Test Data
CREATE TABLE #Table
(
ID [INT] IDENTITY(1, 1)
NOT NULL ,
[col1ID] [INT] NOT NULL ,
[Col2ID] [INT] NOT NULL
)
GO
INSERT INTO #Table
( col1ID, Col2ID )
VALUES ( 1, 1 ),
( 1, 2 ),
( 1, 3 ),
( 2, 1 ),
( 2, 1 ),
( 2, 1 ),
( 3, 2 ),
( 3, 2 ),
( 3, 2 )
GO
Output should look like this:
1. First situation without parameter should give output like this:
Col1|Bit
1 0
2 1
3 1
2. Second situation when you supply parameter for Col2ID, example @parameter = 2 output should look like this:
Col1|Bit
1 0
2 0
3 1
What are best possible ways to handle this two situations ?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

What have you tried so far? How is the column 'Bit' computed?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

What are the business rules?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

priestxandar Right there with Babe Points: 724 More actions · Answer 3

Bit column will be bit data type only need to return 0 or 1.

when you query the temp table you will notice that:

col1ID|col2ID

1 1

1 2

1 3

this should give output only one row.

values in col1ID are grouped for same number here col1ID value is 1,

values in col2ID are different for same col1ID and this makes Bit column to be 0

here is the output.

col1ID|Bit

1 0

col1ID|col2ID

2 5

values in col1ID are grouped for same number here col1ID is 2,

values in col2ID are same for col1ID and this makes Bit column to be 1

this should give output only one row

col1ID|Bit

2 1

So results are grouped by col1ID, when you have same value in col1ID and different value in col2 (for same value in col1ID ) then you have 0 in bit column, otherwise 1.

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

I am sure that your desired output makes perfect sense to you. Unfortunately we don't know your business rules and what you posted does not help clarify what you want. Remember we can't see your screen, we don't know anything about your project and we have no idea what you want to do other than what you have posted. If you can explain the logic we can help with this.

_______________________________________________________________

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/

ScottPletcher SSC Guru Points: 100942 More actions · Answer 5

DECLARE @parameter int

SET @parameter = 2 --NULL=ALL; spec. value = that value

SELECT

col1ID,

CASE WHEN COUNT(DISTINCT Col2ID) = 1 AND

(@parameter IS NULL OR MIN(Col2ID) = @parameter)

THEN 1 ELSE 0 END AS bit

FROM #Table

GROUP BY col1ID

ORDER BY col1ID

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

priestxandar Right there with Babe Points: 724 More actions · Answer 6

ScottPletcher (4/17/2014)
DECLARE @parameter int SET @parameter = 2 --NULL=ALL; spec. value = that value SELECT col1ID, CASE WHEN COUNT(DISTINCT Col2ID) = 1 AND (@parameter IS NULL OR MIN(Col2ID) = @parameter) THEN 1 ELSE 0 END AS bit FROM #Table GROUP BY col1ID ORDER BY col1ID

Awesome solution, exactly i was looking for, many thanks

paul.s.lach Old Hand Points: 329 More actions · Answer 7

I believe this will handle your first case:

SELECT [col1ID]

, CAST(CASE WHEN MIN([Col2ID]) = MAX([Col2ID]) THEN 1 ELSE 0 END AS BIT) AS [Bit]

FROM [#Table]

GROUP BY [col1ID]

ORDER BY [col1ID];

I do not understand what your business rules are for the second case.