Display data in gridview RSS

• • Reply
  

  piram

  Member

  127 Points

  151 Posts

  Display data in gridview

  Apr 10, 2012 02:27 PM|LINK

  Hi, I have an sql server table that looks like this:

  The two columns on the table are Salesman and Project.

  Salesman------Project

  John------------ProjectOne

  Mark-----------ProjectTwo

  John------------ProjectOne

  So, every salesman has a project asigned to him, but also a salesman can have the same project asigned twice. The number of the salesmans and projects can change.

  When retrieving this data from an asp.net website, i would like to load it in a gridview, but i want the data showing like this:

  Salesman------ProjectOne------ProjectTwo-----

  John-----------------2------------------0------

  Mark----------------0------------------1------

  The name of the project on the top columns, the name of the salesman on the left, and on each corresponding cell the number of times a salesman is involved in a project.

  How can i do this?

  thanks.

• • Reply
  

  Basquiat

  Contributor

  2381 Points

  632 Posts

  Re: Display data in gridview

  Apr 10, 2012 02:41 PM|LINK

  SELECT Salesman, SUM(CASE WHEN Project = 'ProjectOne' THEN 1 ELSE 0 END) as ProjectOne, SUM(CASE WHEN Project = 'ProjectTwo' THEN 1 ELSE 0 END) as ProjectTwo
  FROM Table
  GROUP BY Salesman

  • Edited by Basquiat on Apr 10, 2012 02:42 PM
  • Edited by Basquiat on Apr 10, 2012 03:00 PM
• • Reply
  

  mameenkhn

  Contributor

  2026 Points

  391 Posts

  Re: Display data in gridview

  Apr 10, 2012 02:55 PM|LINK

  Select salesman, IsNull(Project1,0) Project1,IsNull(Project2,0) Project2 from
  (
  select salesman, Project ,1 as projectCount from salesproject
  ) up
  pivot (SUM(projectCount) FOR Project in (Project1,Project2)) as pvt
  order by salesman

  use dynamic pivot if you have more than two projects, check the link below

  http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/

  Please 'Mark as Answer' if my post helped you
  --------------------------------------------------
  Muhammad Amin
  محمد امين
  • Edited by mameenkhn on Apr 10, 2012 03:10 PM
• • Reply
  

  piram

  Member

  127 Points

  151 Posts

  Re: Display data in gridview

  Apr 10, 2012 09:06 PM|LINK

  mameenkhn

  Select salesman, IsNull(Project1,0) Project1,IsNull(Project2,0) Project2 from
  (
  select salesman, Project ,1 as projectCount from salesproject
  ) up
  pivot (SUM(projectCount) FOR Project in (Project1,Project2)) as pvt
  order by salesman

  use dynamic pivot if you have more than two projects, check the link below

  http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/

  yea, the number of projects can change, so i guess i need to use dynamic pivot.

  i checked that link and i see its using two tables.

  Is there a way to do it with only one table?

• • Reply
  

  piram

  Member

  127 Points

  151 Posts

  Re: Display data in gridview

  Apr 10, 2012 09:51 PM|LINK

  mameenkhn

  Select salesman, IsNull(Project1,0) Project1,IsNull(Project2,0) Project2 from
  (
  select salesman, Project ,1 as projectCount from salesproject
  ) up
  pivot (SUM(projectCount) FOR Project in (Project1,Project2)) as pvt
  order by salesman

  use dynamic pivot if you have more than two projects, check the link below

  http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/

  also, when trying the example on that link, i create the tables, i insert data in the tables, and then i execute this:

  declare @col varchar(1000)
  declare @sql varchar(2000)
  
  select @col = COALESCE(@col + ', ','') + QUOTENAME(name)
  from table2
  --from (select distinct name from table2) Tbl
  
  
  select @col -- This gives: [loc1], [loc2], [loc3], [loc4], [loc5], [loc6]
  
  -- Now setting this @col variable in the Dynamic SQL.
  set @sql = '
  select number, descr, ' + @col + '
  from (select number, descr, numberatlocation, name
  from table1 join table2 on table1.location=table2.code)p
  PIVOT(MAX (numberatlocation) FOR Name IN ( ' + @col + ' )
  ) AS pvt
  ORDER BY number'
  
  print @sql
  
  exec (@sql)

  
  and i get this output:

  

  is this ok?

  i thought it should only output one query and not two.

• • Reply
  

  sushanth009

  Contributor

  6243 Points

  1168 Posts

  Re: Display data in gridview

  Apr 10, 2012 10:05 PM|LINK

  You need to use a  PIVOT  in your query which shows the rows as columns ...

• • Reply
  

  piram

  Member

  127 Points

  151 Posts

  Re: Display data in gridview

  Apr 10, 2012 10:17 PM|LINK

  sushanth009

  You need to use a  PIVOT  in your query which shows the rows as columns ...

  in this part of the code i posted:

  select number, descr, ' + @col + '
  from (select number, descr, numberatlocation, name
  from table1 join table2 on table1.location=table2.code)p
  PIVOT(MAX (numberatlocation) FOR Name IN ( ' + @col + ' )
  ) AS pvt
  ORDER BY number'

  There's already the PIVOT.

  Is that what you meant?

  • Edited by piram on Apr 10, 2012 10:19 PM
  • Edited by piram on Apr 11, 2012 05:48 PM
• • Reply
  

  Mark-yu

  Participant

  888 Points

  127 Posts

  Re: Display data in gridview

  Apr 12, 2012 03:42 AM|LINK

  Hi, 

  piram

  select @col -- This gives: [loc1], [loc2], [loc3], [loc4], [loc5], [loc6] 

    Just comment it, then should output just one query 

  declare @col varchar(1000)
  select @col = COALESCE(@col + ', ','') + QUOTENAME(Project)
  from (select Distinct Project from Table_1 )tb1
  select @col

     Above will generate the project column. Then you can use PIVOT  to get the data you want

  • Edited by Mark-yu on Apr 12, 2012 05:44 AM
  • Edited by piram on Apr 12, 2012 03:23 PM
  • Marked as answer by Mark-yu on Apr 25, 2012 02:43 AM
• • Reply
  

  mameenkhn

  Contributor

  2026 Points

  391 Posts

  Re: Display data in gridview

  Apr 12, 2012 04:12 AM|LINK

  Dear piram,

  Here is your dynamic pivot query. I didnot test it but Hope this helps

  declare @col varchar(1000)
  declare @sql varchar(4000)
  
  select @col = COALESCE(@col + ', ','') + QUOTENAME(Project)
  from (Select Distinct Project From salesproject) temp
  
  -- Now setting this @col variable in the Dynamic SQL.
  set @sql = '
  select salesman, ' + @col + '
  from (
  select salesman, Project ,1 as projectCount from salesproject
  ) up
  pivot (SUM(projectCount) FOR Project in ( ' + @col + ' )
  ) AS pvt
  ORDER BY salesman'
  
  print @sql
  
  exec (@sql)

  
  
  

  Please 'Mark as Answer' if my post helped you
  --------------------------------------------------
  Muhammad Amin
  محمد امين
  • Edited by mameenkhn on Apr 12, 2012 05:56 AM
  • Marked as answer by piram on Apr 12, 2012 03:23 PM
• • Reply
  

  mameenkhn

  Contributor

  2026 Points

  391 Posts

  Re: Display data in gridview

  Apr 12, 2012 06:05 AM|LINK

  And here is the modified version of dynamic pivot which take cares about nulls

  declare @col varchar(4000)
  declare @sql varchar(4000)
  declare @col2 varchar(4000)
  
  select @col = COALESCE(@col + ', ','') + QUOTENAME(Project)
  from (Select Distinct Project From salesproject) temp
  
  
  select @col2 = COALESCE(@col2 + ', ','') + 'IsNull(' + QUOTENAME(Project)+ ',0) ' +  QUOTENAME(Project)
  from (Select Distinct Project From salesproject) temp2
  
  
  set @sql = 'select salesman, ' + @col2 + '
  from (
  select salesman, Project ,1 as projectCount from salesproject
  ) up
  pivot (SUM(projectCount) FOR Project in ( ' + @col + ' )
  ) AS pvt
  ORDER BY salesman'

  
  
  

  Please 'Mark as Answer' if my post helped you
  --------------------------------------------------
  Muhammad Amin
  محمد امين
  • Edited by mameenkhn on Apr 12, 2012 06:05 AM
  • Marked as answer by piram on Apr 12, 2012 03:23 PM