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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 04/25/2013 :  02:42:46  Show Profile  Reply with Quote
no result and no error
when execute code give me
this line
COALESCE( [1], '0' ), COALESCE( [3], '0' ), COALESCE( [4], '0' ), COALESCE( [5], '0' ), COALESCE( [6], '0' ), COALESCE( [7], '0')

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

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/25/2013 :  02:59:55  Show Profile  Reply with Quote
Have you executed this post (Posted - 04/24/2013 : 08:28:45) ?
Run the above posted query....
I think you have executed earlier than above post...

--
Chandu
Go to Top of Page

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 04/25/2013 :  03:18:58  Show Profile  Reply with Quote
yes ,

1- execute @Pivot
result is
Msg 203, Level 16, State 2, Line 23
The name '[1],[3],[4],[5],[6],[7]' is not a valid identifier.

2-execute @sql
result is >>
Msg 203, Level 16, State 2, Line 23
The name '
SELECT ID_student COALESCE( [1], '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],[3],[4],[5],[6],[7] )
) AS pv ' is not a valid identifier.

3- execute @PivotCols
result is >>>
Msg 203, Level 16, State 2, Line 23
The name 'COALESCE( [1], '0' ), COALESCE( [3], '0' ), COALESCE( [4], '0' ), COALESCE( [5], '0' ), COALESCE( [6], '0' ), COALESCE( [7], '0')' is not a valid identifier.

please can you correct my tried......



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

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/25/2013 :  03:30:39  Show Profile  Reply with Quote
nope... you have to run complete script together

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 ';
EXEC (@sql)

--
Chandu

Edited by - bandi on 04/25/2013 03:33:37
Go to Top of Page

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 04/25/2013 :  03:39:23  Show Profile  Reply with Quote
thanks bandi ,
now , code run and give good result
last question
can you explain this line?
FOR XML PATH('')), 1, 1 , '');

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/25/2013 :  04:18:50  Show Profile  Reply with Quote
quote:
Originally posted by sayer

thanks bandi ,
now , code run and give good result
last question
can you explain this line?
FOR XML PATH('')), 1, 1 , '');

http://aman-services.net
for office
???? ???? ???????


FOR XML PATH builds an XML structure from the relational resultset (ie your SELECT statement) In this case we pass a dummy node ('') to create a dummy xml which will give you comma separated list of columnnames for the pivot

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

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/25/2013 :  04:26:29  Show Profile  Reply with Quote
quote:
Originally posted by sayer

thanks bandi ,
now , code run and give good result
last question
can you explain this line?
FOR XML PATH('')), 1, 1 , '');

You are welcome
Visakh already explained about FOR XML PATH('').....
In this [FOR XML PATH('')), 1, 1 , '')], red marked code is the part of STUFF( (SELECT ....), StartPos, Length, ReplaceString)

--
Chandu
Go to Top of Page

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 04/28/2013 :  12:38:09  Show Profile  Reply with Quote
when create store procedure to use last code (Posted - 04/25/2013 : 03:30:39 )
and call Sp by c# reports no return columns like select statement

how to return columns ?


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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/29/2013 :  00:47:42  Show Profile  Reply with Quote
continued here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184921

------------------------------------------------------------------------------------------------------
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  
Previous 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.08 seconds. Powered By: Snitz Forums 2000