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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to give count() default value

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 1

code
select * from view_1
SELECT
*
FROM
( SELECT
lesson_number,
ID_student,
stae
FROM view_1
where stae='no' or stae='yes'
) AS P
PIVOT
(
Count(stae) FOR lesson_number IN ([1], [2], [3],[4],[5],[6],[7])
) AS pv


[URL=http://www.upislam.com/][/URL]
please help me

http://aman-services.net
for 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'
Go to Top of Page

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 it

http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

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 it

http://aman-services.net
for 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 like
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],
-- etc

Go to Top of Page

sayer
Starting Member

35 Posts

Posted - 2013-04-21 : 10:08:01
please, can you write complete code

the number 1 in image result number of yes or no

and
number 0 in image no result

how to distinct between Number of no and number of yes
look to image

i want to see like this image
please help me



http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

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 P
PIVOT
(
Count(stae) FOR lesson_number IN ([1], [2], [3],[4],[5],[6],[7])
) AS pv
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 P
PIVOT
(
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 1
when count number of yes set Yes Instead of 1
function count return 0 When there are no data of student in this lecture
look at image



http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

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 P
PIVOT
(
Max(stae) FOR lesson_number IN ([1], [2], [3],[4],[5],[6],[7])
) AS pv



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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
or
40 lesson_number
or 5 lesson_number
i waiting.........



http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-24 : 05:35:06
Refer this link for dynamic pivot:
Check Combine Result of three table from PIVOT Query section in the following link
http://beyondrelational.com/modules/2/blogs/555/posts/13685/dynamic-pivot-query-for-cross-tab-result.aspx
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

--
Chandu
Go to Top of Page

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 P
PIVOT
(
Max(stae) FOR lesson_number IN ([1], [2], [3],[4],[5],[6],[7])
) AS pv
thanks

http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

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 it
Let us know if you face any issues while attempting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 numbers
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 @PivotCols


Use the above CSV forms and make out now

--
Chandu
Go to Top of Page

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 it
Let us know if you face any issues while attempting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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.net
for office
???? ???? ???????
Go to Top of Page

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 @PivotCols
SET @sql = @sql + '
SELECT ID_student ' + @PivotCols +
' FROM
( SELECT
lesson_number,
ID_student,
stae
FROM view_1
where stae=''no'' or stae=''yes''
) AS P
PIVOT
(
Max(stae) FOR lesson_number IN ( ' + @Pivot + ' )
) AS pv ';[/code]

--
Chandu
Go to Top of Page

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 it
Let us know if you face any issues while attempting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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.net
for office
???? ???? ???????


post your tried query then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 x
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT lesson_number,
ID_student,
stae
FROM view_1

) AS j
PIVOT
(
max(stae) FOR lesson_number IN ('
+ STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
+ ')
) AS p;';
PRINT @sql;

EXEC sp_executesql @sql;

http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

sayer
Starting Member

35 Posts

Posted - 2013-04-24 : 09:25:56
thanks
Bandi
code not give the result
COALESCE( [1], '0' ), COALESCE( [3], '0' ), COALESCE( [4], '0' ), COALESCE( [5], '0' ), COALESCE( [6], '0' ), COALESCE( [7], '0')
just

http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

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 result
means what is the problem whether getting error or incorrect result?

--
Chandu
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -