October 11, 2012 at 7:38 am
This should be straightforward, I just don't know how to write it, any help is appreciated.
I have 2 tables, one lists information about different account types:
ID notes account1 account2 account3 account4 account5
1 notes 1 0 1 0 1
2 notes 0 1 0 1 0
3 notes 1 0 1 0 0
the account types are type "bit" to indicate yes or no that the notes in the row apply to that account type.
The 2nd table is a user table and there is a field called "lastaccounttype" where it lists the last account type a user has been assigned to. SO it would have:
user lastaccounttype
ericb1 account1
I am trying to wite a query that selects only the rows from the notes table where the bit flag for the note is the same account type as the "lastaccounttype". So in the example above, it would only return rows 1 and 3 where the bit flag for "account1" was set to yes (or 1).
Not sure if I need 5 case statements, or if there is another way to write this? Any help is greatly appreciated, thanks!
October 11, 2012 at 8:18 am
I think an easy options is to Pivot using the UNPIVOT statement
This will transpose your Account1, Account2 columns into one column called "AccountType", then you can do a join on AccountType using the LastAccountType of your 2nd table.
So for your query it would look something like this:
;WITH cte as -- Used a CTE to generate the sample values in your 1st table
(SELECT *
FROM
(VALUES(1, 'notes', 1, 0, 1, 0, 1),(2, 'notes', 1, 0, 1, 0, 1),(3, 'notes', 1, 0, 1, 0, 1)) as t(id,note,ac1,ac2,ac3,ac4,ac5) --SAMPLE VALUES
)
SELECT id, note, AccountTypeID,IncludeNote
FROM
(SELECT id, note, ac1, ac2, ac3, ac4, ac5
FROM cte) p
UNPIVOT
(IncludeNote FOR AccountTypeID IN
(ac1, ac2, ac3, ac4, ac5))AS unpvt;
Then you can just join on AccountTypeID using the 2nd table, and filter by IncludeNote
Hope this helps!
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
October 11, 2012 at 8:37 am
-- sample data
;WITH
MySample (ID, notes, account1, account2, account3, account4, account5) AS (
SELECT 1, 'notes', 1, 0, 1, 0, 1 UNION ALL
SELECT 2, 'notes', 0, 1, 0, 1, 0 UNION ALL
SELECT 3, 'notes', 1, 0, 1, 0, 0),
UserTable (, lastaccounttype) AS (SELECT 'ericb1', 'account1' UNION ALL SELECT 'Yotam Ottolenghi','account5')
-- solution
SELECT s.ID, s.notes, x.*, u.
FROM MySample s
CROSS APPLY (
VALUES
(s.account1,'Account1'),
(s.account2,'Account2'),
(s.account3,'Account3'),
(s.account4,'Account4'),
(s.account5,'Account5')
) x (Flag, [Account])
INNER JOIN UserTable u
ON u.lastaccounttype = x.Account
AND x.Flag = 1
ORDER BY u.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 11, 2012 at 9:55 am
Here's an alternative:
SELECT u.*, n.ID, n.notes
FROM UserTable u
INNER JOIN Notes n ON
(u.lastaccounttype = 'account1' AND n.account1 = 1) OR
(u.lastaccounttype = 'account2' AND n.account2 = 1) OR
(u.lastaccounttype = 'account3' AND n.account3 = 1) OR
(u.lastaccounttype = 'account4' AND n.account4 = 1) OR
(u.lastaccounttype = 'account5' AND n.account5 = 1)
Edit: Added the sql and /code tags around the SQL.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 15, 2012 at 1:06 pm
excellent! Thanks for the sample!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply