Help in creating a query

Question

Post reply

Help in creating a query

dtopicdragovic

Old Hand

Points: 335
More actions
June 17, 2011 at 7:07 am

#389763

Hello everyone!

I am trying to pull out some data from table that looks like this:

ID Customer SUBJECT INTERESTED

1 1 SubjectA True

2 1 SubjectB True

3 1 SubjectC False

4 2 SubjectA True

5 2 SubjectB False

6 2 SubjectC True

What I want to get is result with following columns:

Customer SubjectOfInterest SubjectOfNOInterest

where we would list all customers with their SubjectOfInterest (where Interested = True) and SubjectofNotInterest (where Interested = False)

Can anyone please help me in creating this query?

If you need more info please let me know.

Thanks

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

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

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 1

One approach would be a pivot temp table.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

from the info you have provided i assume that the data is in 1 table so your query should looks something like this

select collum, collum, collum

from table

where collum ='True'

and collumn 2='false

ie

select ID, Customer, SUBJECT, INTERESTED

from customers

where Interested = True

and SubjectofNotInterest = False

***The first step is always the hardest *******

pompy704 Grasshopper Points: 11 More actions · Answer 3

pompy704

Grasshopper

Points: 11

June 17, 2011 at 7:26 am

#1340403

SELECT * into #true

FROM table t

WHERE subectinterested = 'true'

SELECT * into #false

FROM table t

WHERE subectinterested = 'false'

SELECT t.customer, t.subject, f.subject

FROM #true t

INNER JOIN #false f

ON t.customer = f.customer

Would that work?

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 4

Try this



DECLARE @T TABLE(ID INT,Customer INT,SUBJECT VARCHAR(10), INTERESTED VARCHAR(5))

INSERT INTO @T(ID,Customer,SUBJECT,INTERESTED)

SELECT 1, 1, 'SubjectA', 'True' UNION ALL

SELECT 2, 1, 'SubjectB', 'True' UNION ALL

SELECT 3, 1, 'SubjectC', 'False' UNION ALL

SELECT 4, 2, 'SubjectA', 'True' UNION ALL

SELECT 5, 2, 'SubjectB', 'False' UNION ALL

SELECT 6, 2, 'SubjectC', 'True';

SELECT T1.Customer,

       STUFF((SELECT ','+T2.SUBJECT AS "text()"

             FROM @T T2

             WHERE T2.Customer=T1.Customer

               AND INTERESTED='True'

             ORDER BY T2.SUBJECT

             FOR XML PATH('')),

             1,1,'') AS SubjectOfInterest,

       STUFF((SELECT ','+T2.SUBJECT AS "text()"

             FROM @T T2

             WHERE T2.Customer=T1.Customer

               AND INTERESTED='False'

             ORDER BY T2.SUBJECT

             FOR XML PATH('')),

             1,1,'') AS SubjectOfNOInterest

FROM @T T1

GROUP BY T1.Customer

ORDER BY T1.Customer;

____________________________________________________

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

dtopicdragovic Old Hand Points: 335 More actions · Answer 5

Thanks to all for so fast replies!

The last one by Mark seems to be the closest to what I need.

I already have a table that has a same structure as the one created in query. The only thing is that I need to have SubjectsOfInterest on different lines when there is more than one. So in this case Customer 1 would have 3 rows (2 for each SubjectOfInterest and 1 for subjectOfNOInterest)

Any idea?

Thanks

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 6

Couple of possibilities

SELECT Customer,SUBJECT AS SubjectOfInterest,'' AS SubjectOfNOInterest



FROM @T

WHERE INTERESTED='True'

UNION ALL

SELECT Customer,'' AS SubjectOfInterest,SUBJECT AS SubjectOfNOInterest

FROM @T

WHERE INTERESTED='False'

ORDER BY Customer,SubjectOfInterest,SubjectOfNOInterest;

WITH SubjectOfInterest AS (

SELECT Customer,SUBJECT,ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY SUBJECT) AS rn

FROM @T

WHERE INTERESTED='True'),

SubjectOfNOInterest AS (

SELECT Customer,SUBJECT,ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY SUBJECT) AS rn

FROM @T

WHERE INTERESTED='False')

SELECT COALESCE(T1.Customer,T2.Customer) AS Customer,

       T1.SUBJECT AS SubjectOfInterest,

       T2.SUBJECT AS SubjectOfNOInterest

FROM SubjectOfInterest T1

FULL OUTER JOIN SubjectOfNOInterest T2 ON T1.Customer=T2.Customer AND T1.rn=T2.rn

ORDER BY Customer;

____________________________________________________

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

dtopicdragovic Old Hand Points: 335 More actions · Answer 7

dtopicdragovic

Old Hand

Points: 335

June 17, 2011 at 8:32 am

#1340476

This is great!!!! It gives exactley what I was looking for.

Now I have to look in detail and try to understand 🙂

Thank you one more time

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

It's not my job to judge, but nice job, Mark. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions