SQL HELP...Dynamic SQL? 
Author Message
 SQL HELP...Dynamic SQL?
What your are trying to do is called pivoting,
Access and Excel have this built in to there functionality.

To do this totally dynamically in a procedure is going to take
a lot of work. ~700 lines of code.



Wed, 02 Jan 2002 03:00:00 GMT
 SQL HELP...Dynamic SQL?

Kevin,

    Are you using SQL 7?  If so, this would be fairly simply to accomplish
in a sp with a temp table, a cursor and a Alter table add column statement.
If you are using a previous version of Sql Server, it would be more
complicated.

--
Hope this helps.
Please reply to the newsgroup.
Thanks!
glennc



Wed, 02 Jan 2002 03:00:00 GMT
 SQL HELP...Dynamic SQL?
I need help (I will try to use a simple example).
Say you had two tables:
Students (SocSec,Name,ClassID)
Classes (ClassID,Name)

****for the purpose of this question, there are MULTIPLE entries in     the
Students table. (One for each class that the student attends)

The end result I am trying to achieve would have ALL SocSec's going down and
the column heading would be ALL of the available classes. (With an 'X' where
the student was enrolled)
Example:

SocSec    Biology   Math   Science  English  History  .......
111111        X                           X             X
333333        X            X                            X
555555        X                                                        X

PS: Classes will always be added.. I was trying to do this with a view.
                                      Kevin



Thu, 03 Jan 2002 03:00:00 GMT
 SQL HELP...Dynamic SQL?
Hmm...another workaround.
Shall we add this to the list:before triggers,cascading
updates/deletes,udf's,recursive queries........<g>

Steve Dassin

Quote:

> Are you using SQL 7?  If so, this would be fairly simply to accomplish
>in a sp with a temp table, a cursor and a Alter table add column statement.
>[snipe]



Thu, 03 Jan 2002 03:00:00 GMT
 SQL HELP...Dynamic SQL?
I forgot about this method, the last one I did had
a lot of business logic in it.

Students (SocSec,Name,ClassID)
Classes (ClassID,Name)

------------------------------------
create proc ............   as







Create table #classes (class_name varchar(10) null)

--build up a list of column names
Insert into #class (class_name)
Select distinct Name
From Classes
Order by name

Create table #rpt (SocSec varchar(10) null)

Declare cl_name_cur CURSOR for
select class_name from #classes

--1) Build the table.
Open cl_name



BEGIN

null '


END

CLOSE cl_name

--2) Populate the table


Students'
--since the table #classes has been modified at runtime all statemets on the
table need to
--issused dynamically

Open cl_name



BEGIN

'X' +  char(39)  + '  '



'



END

CLOSE cl_name

--Output the data



 --cleanup
DEALLOCATE  cl_name

drop table #classes
drop table #rpt

---------------

This should get you on track -- there may be syntax error in the sample



Thu, 03 Jan 2002 03:00:00 GMT
 SQL HELP...Dynamic SQL?
Yes, I am using SQL Server 7.0
I will try some of your suggestions.....
They are gretly appreciated....
Kevin
Quote:

>Kevin,

>    Are you using SQL 7?  If so, this would be fairly simply to accomplish
>in a sp with a temp table, a cursor and a Alter table add column statement.
>If you are using a previous version of Sql Server, it would be more
>complicated.

>--
>Hope this helps.
>Please reply to the newsgroup.
>Thanks!
>glennc



Fri, 04 Jan 2002 03:00:00 GMT
 SQL HELP...Dynamic SQL?
hi,

posted me an sp_ he wroted to accomplish transform+pivot tasks
hth
Andrea Montanari


Quote:
> Yes, I am using SQL Server 7.0
> I will try some of your suggestions.....
> They are gretly appreciated....
> Kevin

> >Kevin,

> >    Are you using SQL 7?  If so, this would be fairly simply to
accomplish
> >in a sp with a temp table, a cursor and a Alter table add column
statement.
> >If you are using a previous version of Sql Server, it would be more
> >complicated.

> >--
> >Hope this helps.
> >Please reply to the newsgroup.
> >Thanks!
> >glennc



Fri, 04 Jan 2002 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. PL/SQL HELP: Dynamic TABLE name from SQL query

2. PL/SQL:Efficiency of inline SQL vs use of Dynamic SQL Package

3. Dynamic order by without dynamic sql ?

4. dynamic T-SQL for dynamic parameter

5. DYNAMIC SQL MVS DB2 V6 DYNAMIC WHERE

6. Dynamic SQL accessing dynamic temp tables

7. Dynamic Execute Statement vs. Passing Dynamic SQL

8. need help...Using @TABLE variable instead of #temp tables with dynamic SQL (sp_executesql)

9. Help!!! Dynamic SQL

10. Complex and Dynamic SQL Query ----------Please help

11. Help: SQL7 Truncating Dynamic SQL in EXECUTE


 
Powered by phpBB® Forum Software