Sql column adding another column

Question

SELECT * from
  (
    SELECT intUserID as [KidId], intAssessmentID as [assid] ,vchTopic as [Topics] from ManageAssessment WHERE intUserID=174 and IsRorW='yes'

   )AS S
  pivot
  (
  COUNT(KidId)

    FOR assid IN ([22],[19],[16],[17],[18])
   )AS s

Hey its my code to create a Table i want to add other columns but it coming wrongly..! i want a column after column 4 i.e, difference of 2 value.can anyone help me to solve.. Thank you.....enter image description here

Do you mean column `[19]` - `[16]` as a new column? Can you please post the desired results? — Mahmoud Gamal, Dec 31 '13 at 12:54
Not getting exactly what you want to say. _italic_i want a column after column 4 i.e, difference of 2 value._italic_ whats this — eatSleepCode, Dec 31 '13 at 12:54
after column 18 i have to add new column that row should contain difference of column 22 and column 19. — user2783430, Dec 31 '13 at 12:57

Mahmoud Gamal · Accepted Answer · 2014-01-02T07:49:00.410

2

You can do this:

SELECT 
  Topics,
  [22],
  [19],
  [16],
  [17],
  [18],
  [22] - [19] AS NewColumn      
from
(
   SELECT 
     intUserID as [KidId], 
     intAssessmentID as [assid] ,
     vchTopic as [Topics] 
   from ManageAssessment 
   WHERE intUserID=174 and IsRorW='yes'
)AS S
pivot
(
  COUNT(KidId)
  FOR assid IN ([22], [19], [16], [17], [18])
)AS s;

Update:

If you want to add a new row as a total for all new columns, you can do something like this:

WITH CTE AS
(
    SELECT 
      Topics,
      [22],
      [19],
      [16],
      [17],
      [18],
      [22] - [19] AS NewColumn      
    from
    (
       SELECT 
         intUserID as [KidId], 
         intAssessmentID as [assid] ,
         vchTopic as [Topics] 
       from ManageAssessment 
       WHERE intUserID=174 and IsRorW='yes'
    )AS S
    pivot
    (
      COUNT(KidId)
      FOR assid IN ([22], [19], [16], [17], [18])
    )AS s
)
SELECT
  Topics,
  [22],
  [19],
  [16],
  [17],
  [18],
  NewColumn    
FROM CTE
UNION ALL
SELECT
  'Total',
  SUM([22]),
  SUM([19]),
  SUM([16]),
  SUM([17]),
  SUM([18]),
  SUM(NewColumn)
FROM CTE;

edited Jan 02 '14 at 07:49

answered Dec 31 '13 at 12:56

Mahmoud Gamal

78,257
17
139
164

yes Sir,http://stackoverflow.com/questions/9595157/sql-pivot-table-with-column-and-row-totals, i have checked this i could not able meet my requirement,and i cant delete temp table from there,sir if you have any solution you can send me...thank you. – user2783430 Jan 02 '14 at 07:26
@user2783430- Sorry but I can't understand your requirement, do you want to add a new column or a new row? Please edit your question and explain what are you trying to do with some samples. – Mahmoud Gamal Jan 02 '14 at 07:28
@user2783430 - You're welcome any time, Feel free to ask for any more questions any time, good luck :) – Mahmoud Gamal Jan 02 '14 at 07:48
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/44304/discussion-between-user2783430-and-mahmoud-gamal) – user2783430 Jan 02 '14 at 08:11

Sql column adding another column

1 Answers1

Update: