SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to give count() default value
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 04/21/2013 :  02:47:56  Show Profile  Reply with Quote
[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
???? ???? ???????

Edited by - sayer on 04/21/2013 03:32:12

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 04/21/2013 :  04:13:43  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/21/2013 :  04:54:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 04/21/2013 :  07:53:02  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/21/2013 :  10:08:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 04/21/2013 :  17:55:08  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 04/22/2013 :  04:43:38  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/22/2013 :  06:24:25  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 04/22/2013 :  07:06:37  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/24/2013 :  05:26:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/24/2013 :  05:35:06  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/24/2013 :  07:07:41  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 04/24/2013 :  07:19:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/24/2013 :  07:41:17  Show Profile  Reply with Quote
--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

Saudi Arabia
35 Posts

Posted - 04/24/2013 :  08:26:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/24/2013 :  08:28:45  Show Profile  Reply with Quote
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 ';


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/24/2013 :  08:36:36  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/24/2013 :  08:53:48  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/24/2013 :  09:25:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/25/2013 :  00:42:00  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 04/25/2013 :  01:14:57  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000