add THREE table A&B&C coloums

Question

i have table like table_A sno item amount 1 asdf 545.00 table_b sno item amount 1 dfgh 876 table_c sno item amount i have o/p like i want csharp code read table_A table_b table_c item  amount and add two tables A&b&c ... if table_c is null or empty it not considered o/p like asdf 545 dfgh 876 total 1421

sudipta sanyal · Answer

if u are going to do this in c# then that will very difficult process. it can done in database in very simple way. CREATE TABLE #tbl_1 (sno INT IDENTITY(11)name NVARCHAR(100)id INT) CREATE TABLE #tbl_2 (sno INT IDENTITY(11)name NVARCHAR(100)id INT) CREATE TABLE #tbl_3 (sno INT IDENTITY(11)name NVARCHAR(100)id INT) INSERT INTO #tbl_1 VALUES ('kiran''9876') INSERT INTO #tbl_1 VALUES ('praveen''7654') INSERT INTO #tbl_2 VALUES ('raju''245') INSERT INTO #tbl_2 VALUES ('vamshi''386') --------------------------------------------------------- DECLARE @Results TABLE ( name NVARCHAR(100)id INT) INSERT INTO @Results SELECT * FROM ( SELECT nameid FROM #tbl_1 UNION ALL SELECT nameid FROM #tbl_2 UNION ALL SELECT nameid FROM #tbl_3 ) temp SELECT * FROM @Results UNION ALL SELECT 'total' SUM(id) FROM @Results DROP TABLE #tbl_1 DROP TABLE #tbl_2 DROP TABLE #tbl_3

Khan Abrar Ahmed · Answer

Hi You can try this way also DECLARE @tbl_1 AS TABLE (sno INT IDENTITY(11)item NVARCHAR(100)amount money); DECLARE @tbl_2 AS TABLE (sno INT IDENTITY(11)item NVARCHAR(100)amount money); DECLARE @tbl_3 AS TABLE (sno INT IDENTITY(11)item NVARCHAR(100)amount MONEY); INSERT INTO @tbl_1 VALUES ('asdf'545.00); INSERT INTO @tbl_2 VALUES ('dfgh'876); WITH cte (itemamount ) AS ( SELECT itemamount FROM @tbl_1 AS t UNION SELECT itemamount FROM @tbl_2 AS t2 UNION SELECT itemamount FROM @tbl_3 AS t3 ) SELECT itemamount FROM ( SELECT itemamount FROM cte UNION ALL SELECT 'total' SUM(amount) FROM cte ) AS result

Jignesh Trivedi · Answer

hi You can also use Grouping set try CREATE TABLE #tbl_1 (sno INT IDENTITY(11)name NVARCHAR(100)amount money) CREATE TABLE #tbl_2 (sno INT IDENTITY(11)name NVARCHAR(100)amount money) CREATE TABLE #tbl_3 (sno INT IDENTITY(11)name NVARCHAR(100)amount money) INSERT INTO #tbl_1 VALUES ('asdf'545.00) INSERT INTO #tbl_2 VALUES ('dfgh'876) select namesum(amount) as Amount from( select * from #tbl_1 union select * from #tbl_2 union select * from #tbl_3) A GROUP BY GROUPING SETS((name amount) ()) hope this will help you.