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 |
sayer
Starting Member
35 Posts |
Posted - 2013-04-21 : 02:47:56
|
[URL=http://www.upislam.com/][/URL]i want to print no or yes nested 0 and 1code select * from view_1SELECT* FROM ( SELECT lesson_number, ID_student, stae FROM view_1 where stae='no' or stae='yes' ) AS PPIVOT( Count(stae) FOR lesson_number IN ([1], [2], [3],[4],[5],[6],[7])) AS pv [URL=http://www.upislam.com/][/URL]please help mehttp://aman-services.netfor office???? ???? ??????? |
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-04-21 : 04:13:43
|
case when [value]=0 then 'no' case when [value]=1 then 'yes' case else then 'end of world' |
|
|
sayer
Starting Member
35 Posts |
Posted - 2013-04-21 : 04:54:30
|
my problem is when count number of yes and no put 1 how to count number of yes for student and lecture put yes not return number of ithttp://aman-services.netfor office???? ???? ??????? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-21 : 07:53:02
|
quote: Originally posted by sayer my problem is when count number of yes and no put 1 how to count number of yes for student and lecture put yes not return number of ithttp://aman-services.netfor office???? ???? ???????
Sayer, I didn't quite follow what you are asking for - doesn't what LoztInSpace suggested give you what you are looking for? If it did not, post a nice picture like the other two you posted showing what you are looking for. BTW, what LoztInSpace suggested is for the final select. So it would be likeSELECT ID_Student, CASE WHEN [1] = 1 THEN 'Yes' ELSE 'No' END AS [1], CASE WHEN [2] = 1 THEN 'Yes' ELSE 'No' END AS [2], -- etc |
|
|
sayer
Starting Member
35 Posts |
Posted - 2013-04-21 : 10:08:01
|
please, can you write complete codethe number 1 in image result number of yes or noand number 0 in image no result how to distinct between Number of no and number of yeslook to imagei want to see like this image please help mehttp://aman-services.netfor office???? ???? ??????? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-21 : 17:55:08
|
Can you try this?SELECT ID_Student, CASE WHEN [1] = 1 THEN 'Yes' ELSE 'No' END AS [1], CASE WHEN [2] = 1 THEN 'Yes' ELSE 'No' END AS [2], CASE WHEN [3] = 1 THEN 'Yes' ELSE 'No' END AS [3], CASE WHEN [4] = 1 THEN 'Yes' ELSE 'No' END AS [4], CASE WHEN [5] = 1 THEN 'Yes' ELSE 'No' END AS [5], CASE WHEN [6] = 1 THEN 'Yes' ELSE 'No' END AS [6], CASE WHEN [7] = 1 THEN 'Yes' ELSE 'No' END AS [7] FROM ( SELECT lesson_number, ID_student, stae FROM view_1 where stae='no' or stae='yes' ) AS PPIVOT( Count(stae) FOR lesson_number IN ([1], [2], [3],[4],[5],[6],[7])) AS pv |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-22 : 04:43:38
|
sorry i cant understand on what basis you determine which 1 value to be yes or no------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sayer
Starting Member
35 Posts |
Posted - 2013-04-22 : 06:24:25
|
quote: Originally posted by James K Can you try this?SELECT ID_Student, CASE WHEN [1] = 1 THEN 'Yes' ELSE 'No' END AS [1], CASE WHEN [2] = 1 THEN 'Yes' ELSE 'No' END AS [2], CASE WHEN [3] = 1 THEN 'Yes' ELSE 'No' END AS [3], CASE WHEN [4] = 1 THEN 'Yes' ELSE 'No' END AS [4], CASE WHEN [5] = 1 THEN 'Yes' ELSE 'No' END AS [5], CASE WHEN [6] = 1 THEN 'Yes' ELSE 'No' END AS [6], CASE WHEN [7] = 1 THEN 'Yes' ELSE 'No' END AS [7] FROM ( SELECT lesson_number, ID_student, stae FROM view_1 where stae='no' or stae='yes' ) AS PPIVOT( Count(stae) FOR lesson_number IN ([1], [2], [3],[4],[5],[6],[7])) AS pv
code work but when count number of no set NO Instead of 1when count number of yes set Yes Instead of 1function count return 0 When there are no data of student in this lecture look at image http://aman-services.netfor office???? ???? ??????? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-22 : 07:06:37
|
do you mean this then?SELECT ID_student,COALESCE([1],'0'),COALESCE([2],'0'),COALESCE([3],'0'),COALESCE([4],'0'),COALESCE([5],'0'),COALESCE([6],'0'),COALESCE([7],'0') FROM ( SELECT lesson_number, ID_student, stae FROM view_1 where stae='no' or stae='yes' ) AS PPIVOT( Max(stae) FOR lesson_number IN ([1], [2], [3],[4],[5],[6],[7])) AS pv ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sayer
Starting Member
35 Posts |
Posted - 2013-04-24 : 05:26:49
|
yes ,this my goal thanks last question ,how to make lesson_number dynamic not static for example ;sometime i have 30 lesson_number or40 lesson_number or 5 lesson_number i waiting......... http://aman-services.netfor office???? ???? ??????? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
sayer
Starting Member
35 Posts |
Posted - 2013-04-24 : 07:07:41
|
can you re-write code SELECT ID_student,COALESCE([1],'0'),COALESCE([2],'0'),COALESCE([3],'0'),COALESCE([4],'0'),COALESCE([5],'0'),COALESCE([6],'0'),COALESCE([7],'0') FROM ( SELECT lesson_number, ID_student, stae FROM view_1 where stae='no' or stae='yes' ) AS PPIVOT( Max(stae) FOR lesson_number IN ([1], [2], [3],[4],[5],[6],[7])) AS pvthankshttp://aman-services.netfor office???? ???? ??????? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-24 : 07:19:45
|
why cant you try this yourself based on the links provided? Atleast make a try. Dont wait for someone to spoonfeed with each and every solution as you wont gain anything out of itLet us know if you face any issues while attempting------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-24 : 07:41:17
|
--This is the code for getting CSV form of lesson numbersDECLARE @Pivot VARCHAR(4000) = '', @sql VARCHAR(MAX) = '', @PivotCols VARCHAR(MAX) ='COALESCE( 'SET @Pivot = STUFF( (SELECT ',[' + CAST( lesson_number AS VARCHAR) +']' FROM View_1 GROUP BY lesson_number FOR XML PATH('')), 1, 1 , '');SELECT @Pivot SET @PivotCols = @PivotCols + REPLACE( @Pivot, ',', ', ''0'' ), COALESCE( ') + ', ''0'')'SELECT @PivotCols Use the above CSV forms and make out now--Chandu |
|
|
sayer
Starting Member
35 Posts |
Posted - 2013-04-24 : 08:26:42
|
quote: Originally posted by visakh16 why cant you try this yourself based on the links provided? Atleast make a try. Dont wait for someone to spoonfeed with each and every solution as you wont gain anything out of itLet us know if you face any issues while attempting------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
sorry, i tried many times but no get correct solution because not understand how to 'select' and anther keywords in sql work?how to follow code by breakpoints ?http://aman-services.netfor office???? ???? ??????? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-24 : 08:28:45
|
[code]DECLARE @Pivot VARCHAR(4000) = '', @sql VARCHAR(MAX) = '', @PivotCols VARCHAR(MAX) ='COALESCE( 'SET @Pivot = STUFF( (SELECT ',[' + CAST( lesson_number AS VARCHAR) +']' FROM View_1 GROUP BY lesson_number FOR XML PATH('')), 1, 1 , '');SELECT @Pivot SET @PivotCols = @PivotCols + REPLACE( @Pivot, ',', ', ''0'' ), COALESCE( ') + ', ''0'')'SELECT @PivotColsSET @sql = @sql + ' SELECT ID_student ' + @PivotCols +' FROM ( SELECT lesson_number, ID_student, stae FROM view_1 where stae=''no'' or stae=''yes'' ) AS PPIVOT( Max(stae) FOR lesson_number IN ( ' + @Pivot + ' ) ) AS pv ';[/code]--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-24 : 08:36:36
|
quote: Originally posted by sayer
quote: Originally posted by visakh16 why cant you try this yourself based on the links provided? Atleast make a try. Dont wait for someone to spoonfeed with each and every solution as you wont gain anything out of itLet us know if you face any issues while attempting------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
sorry, i tried many times but no get correct solution because not understand how to 'select' and anther keywords in sql work?how to follow code by breakpoints ?http://aman-services.netfor office???? ???? ???????
post your tried query then------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sayer
Starting Member
35 Posts |
Posted - 2013-04-24 : 08:53:48
|
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX)SET @columns = N''SELECT @columns += ID_student,COALESCE(@columns,'['+lesson_number +']'+,'0'+')' FROM ( select lesson_number, ID_student, stae FROM view_1 ) as xSET @sql = N'SELECT ' + STUFF(@columns, 1, 2, '') + 'FROM( SELECT lesson_number, ID_student, stae FROM view_1 ) AS jPIVOT( max(stae) FOR lesson_number IN (' + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '') + ')) AS p;';PRINT @sql;EXEC sp_executesql @sql;http://aman-services.netfor office???? ???? ??????? |
|
|
sayer
Starting Member
35 Posts |
Posted - 2013-04-24 : 09:25:56
|
thanksBandicode not give the resultCOALESCE( [1], '0' ), COALESCE( [3], '0' ), COALESCE( [4], '0' ), COALESCE( [5], '0' ), COALESCE( [6], '0' ), COALESCE( [7], '0')justhttp://aman-services.netfor office???? ???? ??????? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-25 : 00:42:00
|
lesson_number is varchar or integer? >>code not give the resultmeans what is the problem whether getting error or incorrect result?--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-25 : 01:14:57
|
show us your expected results as against actual result you got------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Next Page
|
|
|
|
|