Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
romeck
Starting Member
16 Posts |
Posted - 2013-07-29 : 01:05:30
|
I have table:Name type nradam pupil 1adam pupil 2martha pupil 1martha pupil 1martha pupil 2martha pupil 2martha pupil 2martha pupil 2how can i make a query with gropuing to have :name type nr nrcount nr nr count adam pupil 1 1 2 1martha pupil 1 2 2 4So basicaly each group should be in the same row not like in group statemant that all diffrtent groups in new line. i can use functions but stil it will be probably consume more time to execute.THQ |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-29 : 01:59:31
|
will the values be always 1 and 2?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
romeck
Starting Member
16 Posts |
Posted - 2013-07-29 : 02:16:36
|
quote: Originally posted by visakh16 will the values be always 1 and 2?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
1. yes there will be always just two values in that column (there will be number of two months that user want to check)2. no - what if there are many unspecified values ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-29 : 02:38:47
|
[code]declare @t table(Name varchar(50),[type] varchar(50), nr int ) insert @t values ('adam', 'pupil', 1),('adam', 'pupil', 2),('martha', 'pupil', 1),('martha', 'pupil', 1),('martha', 'pupil', 2),('martha', 'pupil', 2),('martha', 'pupil', 2),('martha', 'pupil', 2)select Name,[type],MAX(case when nr=1 THEN nr END) AS [1],COUNT(case when nr=1 THEN nr END) AS [1],MAX(case when nr=2 THEN nr END) AS [2],COUNT(case when nr=2 THEN nr END) AS [1]from @t group by Name,[type]output----------------------------------------------Name type 1 1 2 1----------------------------------------------adam pupil 1 1 2 1martha pupil 1 2 2 4[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|