selecting Distinct value in a column based on values another column.

Question

Post reply

selecting Distinct value in a column based on values another column.

kishorefeb28

Ten Centuries

Points: 1087
More actions
May 31, 2013 at 11:35 pm

#293952

Hi Experts,
I have 2 columns as below
--------------------------------
taxid tax_Combination
--------------------------------
Bed+Vat ! Vat
Bed+Vat ! Cess
Bed+Vat ! BED
VAT ! Vat
CST ! CST
from a query i will get the Combinations like "Vat,Cess,BED", now i need to write a query where i have to pass these combinations and get the Taxid = 'Bed+Vat' alone.
but when i us "IN" it returns 'Bed+Vat' and 'Vat' also.
but i need to get the taxid for which the combination is exactly what i have given so it should be 'Bed+Vat' alone please help me.
Thank you.

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

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

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

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

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/

kishorefeb28 Ten Centuries Points: 1087 More actions · Answer 2

Dear Sean,

please find the DDL and insert statements below

drop table test

CREATE TABLE TEST

(

COL1 CHAR(10),

COL2 CHAR(20)

)

INSERT INTO TEST (COL1, COL2) VALUES ('Bed+Vat', 'Vat')

INSERT INTO TEST (COL1, COL2) VALUES ('Bed+Vat', 'Cess')

INSERT INTO TEST (COL1, COL2) VALUES ('Bed+Vat', 'BED')

INSERT INTO TEST (COL1, COL2) VALUES ('Vat', 'Vat')

INSERT INTO TEST (COL1, COL2) VALUES ('CST', 'CST')

select * from TEST

this is the resulted table

COL1 COL2

Bed+Vat Vat

Bed+Vat Cess

Bed+Vat BED

Vat Vat

CST CST

from this above table i want to write a select statement to select 'BED+VAT' when i pass the values(Vat,Cess,BED) in col2.

select statement would be

Select Col1 from test where Col2 = 'vat' and Col2 ='cst' and Col2 ='bed'

from this i need the output as 'BED+VAT' alone, i.e., i should get COL1 value with exact combination i have given .

thank you.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

The answer to what you are asking is Select DISTINCT Col1 from test where Col2 = 'vat' or Col2 ='cess' or Col2 ='bed'.

But what is the result you want when you only pass 'vat'? And what if you only pass 'vat' and 'CST'?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

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

Rather than posting the solution, here's an excellent spackle article by Jeff Moden which explains how to do this and extends the functionality too:

http://www.sqlservercentral.com/articles/T-SQL/88244/[/url]

Reading the article will cost you about ten minutes of your time - perhaps double that if you work through the examples. Time well spent.

^{“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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

Would it be something like this?

CREATE TABLE #TEST

(

COL1 VARCHAR(10),

COL2 VARCHAR(20)

)

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'Vat')

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'Cess')

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'BED')

INSERT INTO #TEST (COL1, COL2) VALUES ('Vat', 'Vat')

INSERT INTO #TEST (COL1, COL2) VALUES ('CST', 'CST')

DECLARE @Search_Stringvarchar(50)

SET @Search_String = 'Vat ,Cess ,BED'

;WITH cte AS

(SELECT DISTINCT

COL1,

STUFF((SELECT RTRIM( ',' + COL2) AS 'data()'

FROM #TEST AS t2

WHERE t2.COL1 = t.COL1 FOR XML PATH( '')),1,1,'') AS OneRowVal

FROM #TEST t)

SELECT *

FROM cte

WHERE OneRowVal = @Search_String

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kishorefeb28 Ten Centuries Points: 1087 More actions · Answer 6

Thank u for reply hanshi,

If I pass 'vat' alone output should be VAT from col1

If I pass 'cst' alone output should be CSR from col1

Thank u

HanShi SSC-Dedicated Points: 33506 More actions · Answer 7

kishorefeb28 (6/4/2013)
If I pass 'vat' alone output should be VAT from col1

But how do you know you want the value "VAT" from col1 (4th row) and not the value "Bed+Vat" from col1 (1st row)? Both rows has the value "VAT" in col2.

If the above is depending on values in col2 from other rows (like from the 2nd and 3rd row) then you'll first need to concatenate all values of col2 where the value in col1 is equal. The solution Luis Cazares posted could be more helpfull for this issue.

First define your (complete) business rules and then build your query accordingly.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Does the following help:

CREATE TABLE #TEST

(

COL1 VARCHAR(10),

COL2 VARCHAR(20)

)

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'Vat');

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'Cess');

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'BED');

INSERT INTO #TEST (COL1, COL2) VALUES ('Vat', 'Vat');

INSERT INTO #TEST (COL1, COL2) VALUES ('CST', 'CST');

DECLARE @Search_Stringvarchar(50);

SET @Search_String = 'Vat,Cess,BED';

with UniqueVals as (

select distinct

COL1

from

#TEST

), BaseData as (

select

COL1,

COL2,

cnt = count(*) over (partition by COL1)

from

#TEST

), CheckVals as (

select

COL1,

Item,

rn = count(*) over (partition by Item)

from

UniqueVals

cross apply dbo.DelimitedSplit8K(@Search_String,',')

), CompData as (

select * from CheckVals

intersect

select * from BaseData

)

select COL1, Item as COL2 from CompData;

go

DECLARE @Search_Stringvarchar(50);

SET @Search_String = 'Vat';

with UniqueVals as (

select distinct

COL1

from

#TEST

), BaseData as (

select

COL1,

COL2,

cnt = count(*) over (partition by COL1)

from

#TEST

), CheckVals as (

select

COL1,

Item,

rn = count(*) over (partition by COL1)

from

UniqueVals

cross apply dbo.DelimitedSplit8K(@Search_String,',')

), CompData as (

select * from CheckVals

intersect

select * from BaseData

)

select COL1, Item as COL2 from CompData;

go

drop table #TEST;

go

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Not sure if I missed anything, not really enough test data to be sure.

kishorefeb28 Ten Centuries Points: 1087 More actions · Answer 10

Thanks a ton Luis

it worked with slight modifications.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Luis Cazares (6/3/2013)
Would it be something like this?
CREATE TABLE #TEST
(
COL1 VARCHAR(10),
COL2 VARCHAR(20)
)
INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'Vat')
INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'Cess')
INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'BED')
INSERT INTO #TEST (COL1, COL2) VALUES ('Vat', 'Vat')
INSERT INTO #TEST (COL1, COL2) VALUES ('CST', 'CST')
DECLARE @Search_Stringvarchar(50)
SET @Search_String = 'Vat ,Cess ,BED'
;WITH cte AS
(SELECT DISTINCT
COL1,
STUFF((SELECT RTRIM( ',' + COL2) AS 'data()'
FROM #TEST AS t2
WHERE t2.COL1 = t.COL1 FOR XML PATH( '')),1,1,'') AS OneRowVal
FROM #TEST t)
SELECT *
FROM cte
WHERE OneRowVal = @Search_String

I think there is a problem here, your code is dependent on the order of values in the search string and how they get concatenated:

CREATE TABLE #TEST

(

COL1 VARCHAR(10),

COL2 VARCHAR(20)

)

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'Vat')

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'Cess')

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'BED')

INSERT INTO #TEST (COL1, COL2) VALUES ('Vat', 'Vat')

INSERT INTO #TEST (COL1, COL2) VALUES ('CST', 'CST')

go

DECLARE @Search_Stringvarchar(50)

SET @Search_String = 'Vat ,Cess ,BED';

WITH cte AS

(SELECT DISTINCT

COL1,

STUFF((SELECT RTRIM( ',' + COL2) AS 'data()'

FROM #TEST AS t2

WHERE t2.COL1 = t.COL1 FOR XML PATH( '')),1,1,'') AS OneRowVal

FROM #TEST t)

SELECT *

FROM cte

WHERE OneRowVal = @Search_String;

go

DECLARE @Search_Stringvarchar(50)

SET @Search_String = 'Vat ,BED ,Cess';

WITH cte AS

(SELECT DISTINCT

COL1,

STUFF((SELECT RTRIM( ',' + COL2) AS 'data()'

FROM #TEST AS t2

WHERE t2.COL1 = t.COL1 FOR XML PATH( '')),1,1,'') AS OneRowVal

FROM #TEST t)

SELECT *

FROM cte

WHERE OneRowVal = @Search_String;

go

drop table #TEST;

go

CREATE TABLE #TEST

(

COL1 VARCHAR(10),

COL2 VARCHAR(20)

)

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'Cess')

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'BED')

INSERT INTO #TEST (COL1, COL2) VALUES ('Vat', 'Vat')

INSERT INTO #TEST (COL1, COL2) VALUES ('CST', 'CST')

INSERT INTO #TEST (COL1, COL2) VALUES ('Bed+Vat', 'Vat')

go

DECLARE @Search_Stringvarchar(50)

SET @Search_String = 'Vat ,Cess ,BED';

WITH cte AS

(SELECT DISTINCT

COL1,

STUFF((SELECT RTRIM( ',' + COL2) AS 'data()'

FROM #TEST AS t2

WHERE t2.COL1 = t.COL1 FOR XML PATH( '')),1,1,'') AS OneRowVal

FROM #TEST t)

SELECT *

FROM cte

WHERE OneRowVal = @Search_String;

go

DECLARE @Search_Stringvarchar(50)

SET @Search_String = 'Vat ,BED ,Cess';

WITH cte AS

(SELECT DISTINCT

COL1,

STUFF((SELECT RTRIM( ',' + COL2) AS 'data()'

FROM #TEST AS t2

WHERE t2.COL1 = t.COL1 FOR XML PATH( '')),1,1,'') AS OneRowVal

FROM #TEST t)

SELECT *

FROM cte

WHERE OneRowVal = @Search_String;

go

drop table #TEST;

go