Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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

3873 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
52326 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
52326 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
2242 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
52326 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
2242 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
2242 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
52326 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
2242 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
52326 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
 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.43 seconds. Powered By: Snitz Forums 2000