|
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
|
|
|
|
|
I have tried it and i have got it.dont think wrong on person those who was unknown to u swjam.
|
|
|
|
|
|
Infinite loop?
|
|
|
|
|
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 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
|
|
|
|
|
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
|
|
|
|
|
If I interpreted yhe question correctly, you could use PIVOT[^]
|
|
|
|
|
thanks for your kind help.
|
|
|
|
|
|
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
Thanks in advance !
|
|
|
|
|
I have used bcp and execute it by xp_cmdshell
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
can you please let me know the table structure?
|
|
|
|
|
|
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"
|
|
|
|
|
first paste your stored procedure code here.
do you return any thing in your procedure code?
|
|
|
|
|
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"
|
|
|
|
|
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
|
|
|
|
|
Which function you are using
ExecuteScaler, executenonquery, etc
Please specify
|
|
|
|
|
dbcommand.ExecuteNonQuery();
Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"
|
|
|
|
|
Do you mean ExecuteNonQuery returns -1? If yes, see what the documentation says
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
|
|
|
|
|
yes, tht's a problem, i can the values in the table that means the insert statement was executed, and if an error has occured the insert should be rolledback rite, but this rollback is not happening and m not able to find what the error is.
wht r the ways to find the error that has caused the rollback?
Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"
|
|
|
|
|
In your stored procedure you can use RAISERROR[^] to generate an error and to inform client what has happened.
|
|
|
|