Database Discussion Boards

Re: Convert Columns as Rows

Wendelius19-Jan-09 2:05

Wendelius

19-Jan-09 2:05

You could have a look at pivoting[^].

The need to optimize rises from a bad design.My articles[^]

Re: Convert Columns as Rows

Vignesh Krishnan19-Jan-09 3:12

Vignesh Krishnan

19-Jan-09 3:12

Thanks Buddy and could YOU tel me the best URL or PDF to study abt SQL SERVER REPORTING SERVICES

Re: Convert Columns as Rows

Wendelius19-Jan-09 6:55

Wendelius

19-Jan-09 6:55

Vignesh Krishnan wrote:
Thanks Buddy

You're welcome

Vignesh Krishnan wrote:
the best URL or PDF to study abt SQL SERVER REPORTING SERVICES

Don't know what's best, but you could start from here: Tutorials (Reporting Services)[^]

The need to optimize rises from a bad design.My articles[^]

Re: Convert Columns as Rows

Vignesh Krishnan19-Jan-09 8:45

Vignesh Krishnan

19-Jan-09 8:45

Hi

I have records like this
Say
Tme_On /dial /connect /month
1 1 1 jan
2 3 4 Dec
3 3 5 nov

I want this to be shown as
Total Jan Dec Nov
Tme_On 1 2 3
Dial 1 3 3
Connect 1 4 5

Can any one Help me ...

Re: Convert Columns as Rows

swjam19-Jan-09 11:26

swjam

19-Jan-09 11:26

Like Mika said use pivots. If you can't even have a look at it and at least pattern from a sample solution to fit your problem then I think you're in the wrong industry... Or is the codez urgentz?

----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list

Re: Convert Columns as Rows

Vignesh Krishnan20-Jan-09 2:10

Vignesh Krishnan

20-Jan-09 2:10

I have tried it and i have got it.dont think wrong on person those who was unknown to u swjam.

Re: Convert Columns as Rows

ColinM12319-Jan-09 11:45

ColinM123

19-Jan-09 11:45

http://www.codeproject.com/script/Forums/View.aspx?fid=1725&select=2886125#xx2886125xx[^]

Online Guitar Tools

Re: Convert Columns as Rows

Wendelius20-Jan-09 8:17

Wendelius

20-Jan-09 8:17

Infinite loop? Smile | :)

The need to optimize rises from a bad design.My articles[^]

Re: Convert Columns as Rows

Wendelius20-Jan-09 8:15

Wendelius

20-Jan-09 8:15

I strongly encourage you to read the usage examples and test different variations using your tables. But to get you to start: For example, to get the first row of your data, you would fetch tme_on and month. I added an inline view since I don't have your table but you would replace that with a select to your table. The same idea goes with the other rows.

select 'tme_on' as heading, [jan],[dec], [nov]
from (
	select 1 as tme_on, 'jan' as [month]
	union
	select 2 as tme_on, 'dec' as [month]
	union
	select 3 as tme_on, 'nov' as [month]
) alias
pivot (
    sum(tme_on)
    for [month] in ([jan],[dec], [nov])
) as resulttable

The need to optimize rises from a bad design.My articles[^]

MS Access Query: Bit value in a Where Clause

swjam19-Jan-09 0:03

swjam

19-Jan-09 0:03

The Query inside my MS Access database looks like the following:

Code:

SELECT 
	dbo_MyTab1.MyCol4, dbo_MyTab2.MyCol3
FROM 
	dbo_MyTab2 INNER JOIN dbo_MyTab1
		ON dbo_MyTab2.MyCol2 = dbo_MyTab1.MyCol2
WHERE 
    (((dbo_MyTab1.MyCol1)=0)
	AND dbo_MyTab1.MyCol3 NOT LIKE '%Expr%')
    AND ((dbo_MyTab2.MyCol4)=False)));

Given that in the backend database:

Code:

MyTab1.MyCol1 is (bit, not null)
MyTab2.MyCol4 is (Flag(bit), not null)

The problem is that when I execute the query, it prompts a dialog box asking for the value of MyTab1.MyCol1.
There should be none of this dialog pop-up. Anyone encountered this before?

Hope this helps make the problem clearer?

----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list

vertical to Horzontal data display

Learner52018-Jan-09 23:22

Learner520

18-Jan-09 23:22

i got 2 tables Reports_tbl and Manger_tbl
Reports_tbl
Manager DepID NumOfEmp_Reports
1 1 2
1 2 5
1 3 1
2 1 13
2 2 10
2 3 0
3 1 1
3 2 2
3 3 9

Manager_tbl
ManagerID ManagerName
1 Sam
2 Joseph
3 Paul

assuming that DeptID as Fin =1, Mng =2 and Sec =3 because i have to show Name of departments and NumOfEmp_Reports to sam,joeph,and paul

i have written query

select m.ManagerName, r.NumOfEmp_Reports as Fin ,r.NumOfEmp_Reports as Mng ,r.NumOfEmp_Reports as Sec
from Manager_tbl as m
Inner join Rerports_tbl as r
on m.managerId =r.mangaerId

the result of this query is as

ManagerName Fin Mng Sec
Sam 2 2 2
Sam 5 5 5
sam 1 1 1
Joseph 13 13 13
Joseph 10 10 10
Joseph 0 0 0
Paul 1 1 1
Paul 2 2 2
Paul 9 9 9

but i have to diplay result as following

ManagerName Fin Mng Sec
Sam 2 5 1
Joseph 13 10 0
Paul 1 2 9

thnx in adv for any kind help.
regards learner

Re: vertical to Horzontal data display

Wendelius19-Jan-09 2:17

Wendelius

19-Jan-09 2:17

If I interpreted yhe question correctly, you could use PIVOT[^]

The need to optimize rises from a bad design.My articles[^]

Re: vertical to Horzontal data display

Learner52019-Jan-09 3:12

Learner520

19-Jan-09 3:12

thanks for your kind help.

Re: vertical to Horzontal data display

Wendelius19-Jan-09 6:52

Wendelius

19-Jan-09 6:52

You're welcome

The need to optimize rises from a bad design.My articles[^]

Save Query Result as XML File in SQL Server 2005

Abhijit Jana18-Jan-09 23:22

Abhijit Jana

18-Jan-09 23:22

Hi,

My stored procedure returning XML data (I am using FOR XML Auto ) . I want to save the same as XML File from that procedure itself . I am using SQL Server 2005 .
Any Idea to implement  Smile | :)

Thanks in advance !

cheers,
Abhijit
CodeProject MVP
My Recent Article : Exploring Session in ASP.Net

Done !!!

Abhijit Jana19-Jan-09 2:04

Abhijit Jana

19-Jan-09 2:04

I have used bcp and execute it by xp_cmdshell Smile | :)

cheers,
Abhijit
CodeProject MVP
My Recent Article : Exploring Session in ASP.Net

Re: Done !!!

Wendelius19-Jan-09 8:50

Wendelius

19-Jan-09 8:50

Just as an alternative because xp_cmdshell has some security issues, I think you could create a linked server against ODBC DSN and use ODBC Text Driver. Using that, you could write to the linked server as it was a table.

The need to optimize rises from a bad design.My articles[^]

How to get leaf nodes of a particular Parent using sql Query

vidhyap18-Jan-09 23:10

vidhyap

18-Jan-09 23:10

Hi,

I have to query the table,which have more than 3 or 4 level of hierarchy.
I need to get all the leaf nodes of particular Parent.i tried Querying like this-select * from Hierarchy where UniqId not in (select Parent from Hierarchy).but it returns leaf nodes of all the parent.what i need is to get leaf nodes of particular Parent.

can anybbody please help me to solve this issue,its realy very important.

Thanks and regards
Vidhya

Re: How to get leaf nodes of a particular Parent using sql Query

kingindra18-Jan-09 23:16

kingindra

18-Jan-09 23:16

can you please let me know the table structure?

Re: How to get leaf nodes of a particular Parent using sql Query

Wendelius19-Jan-09 2:09

Wendelius

19-Jan-09 2:09

You could use CTE (Common Table Expression) recursively possibly in an inline view and then restrict based on level (meaning there are no children under current node). Have a look at: http://msdn.microsoft.com/en-us/library/ms186243.aspx[^]

The need to optimize rises from a bad design.My articles[^]

Insert Stmt returns -1

AB777118-Jan-09 19:43

AB7771

18-Jan-09 19:43

Hi all,
I have written an insert query in a Stored Proc,
It works fine and inserts an record in the table, but
it returns -1 when i execute it thru C# Code.

Can anyone tell me why this happens?

Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"

Re: Insert Stmt returns -1

Reza Raad18-Jan-09 20:30

Reza Raad

18-Jan-09 20:30

first paste your stored procedure code here.
do you return any thing in your procedure code?

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

Re: Insert Stmt returns -1

AB777118-Jan-09 20:39

AB7771

18-Jan-09 20:39

i dont return any thing from the proc, but the default value returned from an insert proc is 1,
in my case donno y it returns -1.
I have done some calculations into some variables and a simple insert statement is used to
insert the record in the table,
I have also used a transaction for this and
after the insert statment i have checked if the @intErrorCode is not 0 then rollback transaction,
The insert works fine but it returns -1.

Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"

Re: Insert Stmt returns -1

Reza Raad18-Jan-09 21:21

Reza Raad

18-Jan-09 21:21

if you call another stored procedure for insert into this procedure?
if this is true:
call to another procedures into one procedure cause returning -1

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

Re: Insert Stmt returns -1

Aman Bhullar18-Jan-09 20:45

Aman Bhullar

18-Jan-09 20:45

Which function you are using
ExecuteScaler, executenonquery, etc

Please specify

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.