February 17, 2009 at 7:56 am
I have two tables, say TableA and TableB.
TableA has column and data as follow
Item |Value
-------------
1 |A
1 |A
1 |B
2 |A
2 |B
2 |B
2 |C
TableB has following columns
Item |NumofValues|NumofA|NumofB|NumofC|
---------------------------------------------
1 |3 |2 |1 |0 |
2 |4 |1 |2 |1 |
Here for TableB we need to insert data from TableA. So NumofValues will have number of occurrence of the given item, NumofA will be number of occurrence of A for the given item and so on.
I can create query that will insert items one at a time, but I want a query which can insert all the values in the TableB from TableA at once.
So can you please help me. I shall be highly grateful.
Thanks in advance.
February 17, 2009 at 8:22 am
DROP TABLE #TableA
CREATE TABLE #TableA (Item INT, Value VARCHAR(1))
INSERT INTO #TableA (Item, Value)
SELECT 1, 'A' UNION ALL
SELECT 1, 'A' UNION ALL
SELECT 1, 'B' UNION ALL
SELECT 2, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 2, 'C'
-- simple aggregate
SELECT Item, Value, COUNT(*)
FROM #TableA
GROUP BY Item, Value
ORDER BY Item, Value
-- rows to columns
SELECT Item, COUNT(*) AS NumofValues,
SUM(CASE Value WHEN 'A' THEN 1 ELSE 0 END) AS NumofA,
SUM(CASE Value WHEN 'B' THEN 1 ELSE 0 END) AS NumofB,
SUM(CASE Value WHEN 'C' THEN 1 ELSE 0 END) AS NumofC
FROM #TableA
GROUP BY Item
ORDER BY Item
-- UPDATE...FROM... (create TableB for this to run)
UPDATE b SET
NumofValues = d.NumofValues,
NumofA = d.NumofA,
NumofB = d.NumofB,
NumofC = d.NumofC
FROM TableB b
INNER JOIN (
SELECT Item, COUNT(*) AS NumofValues,
SUM(CASE Value WHEN 'A' THEN 1 ELSE 0 END) AS NumofA,
SUM(CASE Value WHEN 'B' THEN 1 ELSE 0 END) AS NumofB,
SUM(CASE Value WHEN 'C' THEN 1 ELSE 0 END) AS NumofC
FROM #TableA
GROUP BY Item
ORDER BY Item
) d ON d.Item = b.Item
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
February 17, 2009 at 1:31 pm
Thank you very much. Very appreciated. From now on I will try to post as per the instruction. Thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply