|
What database are you using - SQL 2005 onwards provided the WITH; command whicah can be used for recursive tree patterns like this.
|
|
|
|
|
hi,
i have the following table.
Parent ID.......ID.......Name
NULL..........10001.....Region1
NULL..........10002.....Region2
NULL..........10003.....Region3
10001.........501.......Test1
10001.........502.......Test2
10002.........503.......Test3
10002.........504.......Test4
10003.........505.......Test5
10003.........506.......Test6
501...........507.......Test7
501...........508.......Test8
502...........509.......Test9
502...........510.......Test10
Actually im doing a Treeview control in my ASP.NET application.
I can see the Parent Name, and its child popping out, BUT how to see the ID (501, 502) is having child in ParentID??
My SQL in ASP.NET is "SELECT ID, ParentID, Name FROM Test"
In order to show Child in the Parent, the ParentID has to be NULL.
Any idea??
|
|
|
|
|
You can get a clue from a simple SQL query like
Select distinct ID, ParentID, Name from dbo.tblTest where ID In (Select ParentID from dbo.tblTest)
The output would be like
ID ParentID Name
----------- ----------- -------------
501 10001 Test1
502 10001 Test2
10001 NULL Region1
10002 NULL Region2
10003 NULL Region3
|
|
|
|
|
How can I set a password for DB file and just open it in C# ?
|
|
|
|
|
SajjadZare wrote: How can I set a password for DB file and just open it in C# ?
You want to set SQL Server Password using C# ? Or You want to open a database which is already password protected ?
If you want to open the database which has password, then you need to mention it in ConnectionString .
|
|
|
|
|
I think you are asking how to open a .MDF without having SQL Server, that is open the mdf directly from C#.
You can't, sql server requires a SERVER. I'm not sure if SQL Express will allow you to open the mdf directly.
Access will allow you to do this but you need the Jet engine installed.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SQL Express requires the DB Engine also.
I don't speak Idiot - please talk slowly and clearly
I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury
Driven to the arms of Heineken by the wife
|
|
|
|
|
Hello everyone,
I have simple query as:
SELECT so.title,max(so.nu_view)
FROM song as so,singer as si
GROUP BY so.title,so.nu_view
certainly, it will show the result, but to get achievement it needs a performing process, how to view this activity
Example: how to see combination between song and singer, how to see "group by" activity ...
does SQL server 2005 support this fuction ?
help me
|
|
|
|
|
You can use SQL Server Profiler to check the Performance of your Any Query.
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
|
oh thanks, but sql server trace rewrite action process, not describe action inside sql server 2005,
Example before publish the result , there are many action such as : combination between many tables, action " WHERE" , "GROUP BY" , I want know the its ' detail
help me
|
|
|
|
|
Generally speaking your queries are executed (processed) in the following order:
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
hi guys
i have a filed in my database named status which values is a bit either 0 or 1 ,
i want to get its values and wanto to translate 0 to inactive and 1 to active
and i want to do it at server end in a udf ,how can i do that ? regards .
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
Hope you are looking for this.
select <br />
case when status=0 then 'inactive'<br />
when status=1 then 'active' <br />
end as [Status]<br />
from tablename
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
thanks i was just looking for that .
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I would use and else to trap the 2, -2 values
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi experts
I am using SQL Server 2005
I have Table Having Str. like
__________________________
Heading | Tax
__________________________
A | Taxx
A | Taxz
A | TAXN
B | 10
B | 20
B |30
C |5
C |5
C |4
_____________________________
i Want Result
like
--------------------------------
A B C
----------------------------------
TaxX 10 5
TaxZ 20 5
TaxN 30 4
--------------------------------
The No's of Column also dynamic
i.e A B C D .... so on
then help but Query in Using to get this Result
Dinesh
|
|
|
|
|
I believe you're looking for something like the following:
USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) AS p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
This may help Pivot article[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have set the EMP_ID column in my database as the Primary Key. I mean this is the primary key column. And when I try to insert values in my database table from the form in then it gives me an error as:
"Cannot insert explicit value for identity column in table 'Employees' when IDENTITY_INSERT is set to OFF."
So, how is the IDENTITY_INSERT set to on. Where it is actually located so that I can modify it and start to insert values into my table.
Moreover, when I remove the primary key from this column(EMP_ID) and uncheck the Allow Nulls checkbox then also my same problem persists.
Kindly help me out. I am currently using SQL Server 2005.
|
|
|
|
|
it depends on what you want to do. if you want to enable insertion on identity column
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF } otherwise remove identity from the emp_id column and insert the value from field
|
|
|
|
|
sorry typo error i mean form not field
|
|
|
|
|
I think you are trying to insert a value into a identity column. If you are inserting the value into intentionally then you have to set Identity_insert tablename on/off. else ignore this column while inserting, sql server automatically inserts records into this field.
|
|
|
|
|
If you have access to the Management Studio of SQL Server, try and check the property for the column of your table in design mode, check the Identity Specification property...
|
|
|
|
|
There is a table called QCR100 containing the following information
ITEM_CODE ITEM_DESCRIPTION DCODE AMOUNT
111200 investments 24000003 1630000000.0000
111200 investments 126000005 1630000000.0000
111600 Other Assets 133000009 1700000000.0000
111600 Other Assets 150000002 1730000000.0000
111701 Leasehold 190010130 1740000000.0000
111705 Furniture 190010036 1750000000.0000
111706 Computer Software 190010141 46995799.1800
Then I created a view with this command statement:
SELECT CASE WHEN LEFT(e.dcode, 1) = '1' THEN isnull(SUM(a.Dr_bal_lcy - a.CR_BAL_LCY), 0) WHEN LEFT(e.dcode, 1)
= '2' THEN isnull(SUM(a.CR_BAL_LCY - a.Dr_bal_lcy), 0) ELSE 0 END AS amount, e.ITEM_DESCRIPTION, e.ITEM_CODE
FROM dbo.GLTEMP_CONS AS a RIGHT OUTER JOIN
dbo.QCR100 AS e ON LTRIM(a.GL_code) = e.Dcode
GROUP BY e.ITEM_DESCRIPTION, e.ITEM_CODE, e.Dcode
How I can alter this view to sum the amount for any duplicated item code and group by item code
Example
ITEM_CODE ITEM_DESCRIPTION DCODE AMOUNT
111200 investments 24000003 1630000000.0000
111600 Other Assets 133000009 1700000000.0000
111701 Leasehold 190010130 1740000000.0000
111705 Furniture 190010036 1750000000.0000
111706 Computer Software 190010141 46995799.1800
|
|
|
|