|
Hello Friends,
I've a table as shown below
ID RefID
1 0
2 1
3 2
4 1
5 1
and i want to display it's output as shown below but i'm not getting the way
please suggest a way
ID RefID Total
1 0 3
2 1 1
3 2 0
4 1 0
5 1 0
|
|
|
|
|
This is one solution if the table is small, it requires a seperate sub select for each record
Select A.ID, A.RefID, (Select count(*) from Table where RefID = ID) Total
From Table
For a more efficient method on a large table I would use a LEFT join to itself on TableA.ID = TableB.RefID then use Isnull, a case statement and count to get the same result from a large table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this
declare @tbl table(id int identity, refid int)
insert into @tbl
select 0 union all select 1 union all
select 2 union all select 1 union all
select 1
select t.id,t.refid,case when x.cnt is null then 0 else x.cnt end as total
from @tbl t
left join
(
select refid,count(refid) as cnt
from @tbl
where refid <> 0
group by refid
having (count(refid)>0)) x
on t.id = x.refid
Output :
id refid total
1 0 3
2 1 1
3 2 0
4 1 0
5 1 0
Niladri Biswas
|
|
|
|
|
I have some RDL that was written using the Business Intelligence Design Studio (BIDS) in Visual Studio 2005. This report has a subreport that is visible depending upon a value in the dataset that is the source for the reports data. I have an expression in the hidden property for the subreport that returns true or false depending upon the value. The expression is like =iif(Fields!ShowSubreport.Value = 0, False, True). Anyway everything works fine in Visual Studio 2005. However, I am trying to get this report to work with VS 2008. It is my understanding that when I open this report using BIDS in VS 2008 that it will automatically convert the RDL from 2005 to 2008. It does this this changing my Lists to Tablix and other things. However, when I view the report now, I cannot see the data in the main body of the report. But if I get rid of the expression if the hidden property of the subreport, I can see everything just fine. For example, if I change the hidden property from the expression above to ‘False’ it works just fine. I have tried several different ways of writing the expression, but nothing seems to work. Does anyone know what could be causing this?
|
|
|
|
|
Hi,
i want to copy the database from one system to pen drive and then add that database to another system.
How can i achieve this ?
Thank you
|
|
|
|
|
right click on database->tasks->restore->database.
you wil get a window select the radio button From device and then click on .. you wil get another window then click on add and choose your database from the pen drive.
|
|
|
|
|
sarang_k wrote: you wil get another window then click on add and choose your database from the pen drive.
No it doesn't let me store in pen drive.
what is backup option under task if restore does this job as you said ??
|
|
|
|
|
Yes.. Removable devices will not be listed in Backup folder browser. So backup in anyfolder and then copy it to your pen drive.
Backup option means you make a compressed file that could produce the exact same database instance.
If you have a backup file you can restore it to any sql server instance easily.
Are you clear now ?
|
|
|
|
|
As sarang said, you can also create a new database on your target system from a backup, IE restore to an new database
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
1. Right click the source database, select Tasks --> Backup.
2. Specify a backup location on your hard disk.
3. After the backup process is complete, copy the backup file to your pen drive.
4. On the destination server, copy the backup file to your server's hard disk.
5. From SSMS, right click 'Databases' folder and select Tasks --> Restore. Follow the instructions from there.
|
|
|
|
|
Hi all,
I am using a SQL Server 2008 and created a procedure,but can any one tell me how to use Case in it.
Thanks in advance.
|
|
|
|
|
Case is used to implement IF - Else construct in a query. Use like this
Case <expression>
when 0 then <expression>
when 1 then <expression>
ELSE <expression>
END
Or use
CASE WHEN <expression> = 0 THEN <expression> WHEN <expression> = 1 THEN <expression> ELSE <expression> END
Therefore you can easily incorporate your logic around the select query using CASE structure.
For instance :
Select CASE WHEN AGE > 50 THEN 'AGED' ELSE 'YOUNG' END FROM RECORDS
This will produce AGED if age is greater than 50 otherwise YOUNG.
For detail see this :
http://msdn.microsoft.com/en-us/library/aa258235(SQL.80).aspx[^]
Cheers
|
|
|
|
|
If you dont want this in Select query.. inside your stored procedure.. rather you want to place a logic use
If - Else instead.
|
|
|
|
|
Am I the only person in the world that uses Google when I'm trying to find information? 2 seconds on Google will give you approximately 14 million search results about this including some very good explanations of what CASE is and what it does and how to use it. Just type "sql server case" and away you go.
|
|
|
|
|
Some people seem to find it easier to type in a question rather than trawl through 14 million results! Ah I crack me up. Just because they can find excellent examples in the first 2-3 results is no reson not to be spoon fed. Send me the codz.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Send me the codz.
When you get them can you forward them to me? My homework is soooo important!
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Hi, don't know what is ur actual doubt because the problem stated
sarang_k wrote: How to use the Case in stored procedure
is a normal thing.
If you are looking for syntax there is plenty in google with examples too.
However, I have given 2 sample for the same program
Sample 1:
Create procedure TestCaseStatement
as
Begin
declare @tbl table(id int)
insert into @tbl
select 0 union all select 1 union all
select 2 union all select 1 union all
select 1
select
id
,case when id = 1 then 'One' else 'Other Number' end as Value
from @tbl
End
Sample 2:
Create procedure TestCaseStatement
as
Begin
declare @tbl table(id int)
insert into @tbl
select 0 union all select 1 union all
select 2 union all select 1 union all
select 1
select
id
,Value = case when id = 1 then 'One' else 'Other Number' end
from @tbl
End
The output
id Value
0 Other Number
1 One
2 Other Number
1 One
1 One
As you can see that I have shown 2 ways of holding the case result into a pseudo column.
And as Mr. Mycroft stated, if you have any specific doubt, please don't hesitate to share it here. We all would love to help you.
Hope you understand and ur doubt as per the question title has been answered.
Have a nice day
Niladri Biswas
|
|
|
|
|
Hello All,
Sorry to bother you but I am hitting up against a wall trying to figure something out so I am turning to you for assistance (if possible.).
I have a table of let's say widgets. And for each entry there is a Quantity column. What I am needing to do is to write a Store Procedure for SQL Server that will pull all widgets for a certain customer and create a record in another table for each widget. No problem there. But I need to create a record for each widget based on it's quantity column.
So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces.
What I need to end up with is a new table with 9 entries. 1 for each widget piece.
Again I am sorry to bother you guys, but I am really at a lost.
I thank you in advance for any help you can direct me with.
Fred
|
|
|
|
|
So break it up into smaller bits.
open management studio and connect to the database - this is where you should be doing your test/creating
select *
From Widgets
Where CustomerID = @CustID
That should get you the list of widgets for the customer
Now you want to count the record for each widget type
select count(*),WidgetTypeID
From Widgets
Where CustomerID = @CustID
GroupBy WidgetTypeID
Now add the insert statement around the select
Insert OtherTable(CustID,WidgetTypeID, Qty)
select CustID, WidgetTypeID, count(*)
From Widgets
Where CustomerID = @CustID
GroupBy CustID, WidgetTypeID
Here endeth the lesson
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
According to the OP, this part of it is not a problem. There seems to be some sort of additional requirement which is the sticking point. I'm not entirely sure I understand the problem, but it seems to involve creating more rows in the target table than exist in the source table. 1 customer with 3 widgets should give 9 rows. That's the bit I don't quite get.
|
|
|
|
|
Seems like we are all struggling with this convoluted logic. I'll wait till the OP comes back with some clarification
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Are you looking for this
declare @tblCustomers table(CustID int,CustomerName varchar(50))
declare @tblWidgets table(CustID int,WidgetTypeID int, Qty int)
declare @tblTarget table(CustID int,WidgetTypeID int, Qty int)
insert into @tblCustomers values(101,'Cust1'),(102,'Cust2'),(103,'Cust3')
insert into @tblWidgets values(101,1,5),(101,2,1),(101,3,3),(102,10,5),(103,4,1)
--Program starts
insert into @tblTarget
select CustID,WidgetTypeID,Qty
from
(
select w.CustID,w.WidgetTypeID,w.Qty from @tblWidgets w
inner join @tblCustomers c
on c.CustID = w.CustID
)X where X.CustID =101
select * from @tblTarget
The output being
CustID WidgetTypeID Qty
101 1 5
101 2 1
101 3 3
Niladri Biswas
|
|
|
|
|
ffowler wrote: So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces.
What I need to end up with is a new table with 9 entries. 1 for each widget piece.
9 entries? How do you get 9 entries from this? I can't quite follow that logic.
|
|
|
|
|
Flash of insight. The number of rows to create in the target table depends on the quantity in the source table.
So, customer A buys 3 widget items, Widget1 5 pieces, Widget2 1 piece and Widget3 3 pieces, what you want in the final result table is:
CustId WidgetID
A W1
A W1
A W1
A W1
A W1
A W2
A W3
A W3
A W3
In other words, 9 rows. Is that right?
|
|
|
|
|
That is correct David. I need a row created based on what is in the Quantity field of the original table. You have outlined it perfectly. Sorry I didn't make it clearer to you others originially.
Now, can it be done, or do I have do something externally?
|
|
|
|