May 9, 2011 at 7:35 pm
Hello,
I am working on sql2005 and I want to create view for one table.
Table details :
table name : groups
feilds:
groupnm
debit
credit
and etc.
I want to create view with these three columns. I want to search on Assets and Liability. It should create six columns. three for assets,debit and credit and other three Liability,debit and credit.
I use the queary but it display only three columns and the data is combine I want to display it in seperate columns.
How can I perform this task.
Thanks.
May 9, 2011 at 9:05 pm
Based on what you have said stated without putting your exact table structure to get what you are wanting you can join the table back on itself however you need some sort of column that you can join back on.
Here is an example for you to have a look at :
Use TempDB
Go
Create Table #Groups (
CustomerID int,
groupnm varchar(50),
debit int,
credit int
)
Insert Into #Groups Values (1,'Assets',10,0)
Insert Into #Groups Values (2,'Assets',20,0)
Insert Into #Groups Values (3,'Assets',40,0)
Insert Into #Groups Values (4,'Assets',0,10)
Insert Into #Groups Values (5,'Assets',0,20)
Insert Into #Groups Values (6,'Assets',0,30)
Insert Into #Groups Values (1,'Liabilities',10,0)
Insert Into #Groups Values (2,'Liabilities',20,0)
Insert Into #Groups Values (3,'Liabilities',40,0)
Insert Into #Groups Values (4,'Liabilities',0,10)
Insert Into #Groups Values (5,'Liabilities',0,20)
Insert Into #Groups Values (6,'Liabilities',0,30)
Select g1.CustomerID ,g1.groupnm, g1.debit,g1.credit,G2.groupnm,G2.debit,g2.credit
From #Groups g1 Inner Join #Groups g2 on g1.CustomerID = g2.CustomerID
where g1.groupnm = 'Assets'
and g2.groupnm = 'Liabilities'
Drop table #Groups
Results :
CustomerIDgroupnmdebitcreditgroupnmdebitcredit
1Assets100Liabilities100
2Assets200Liabilities200
3Assets400Liabilities400
4Assets010Liabilities010
5Assets020Liabilities020
6Assets030Liabilities030
I think this is what you are wanting your view to return and look like. I hope this helps.
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 9, 2011 at 11:33 pm
Thank you for so fast reply.
actually I don't understand how to post the code . Next time i will take care of it.
I use this queary but the view do not displaying any data.
I did some change in this view insted of using AND I use OR. but table show only three columns.and data disply one by one.
Thanks....
May 9, 2011 at 11:57 pm
I show you table details:
table name group
feild
grpnm
supergroup
debit
credit
Table data:
grpnm supergroup debit credit
cash Assets 10 10
Loan Assets 20 20
crt lia Liability 30 30
taxes Liability 40 40
the view should be like this:
grpnm supgrp deb crd grpnm supgrp deb crd
Cash Assets 10 10 crt lia Liability 30 30
Loan Assets 20 20 Taxes Liability 40 40
This Is what I want to do.
Please tell me the solution.
May 10, 2011 at 1:07 am
Unless you have a column with values that allow you to link the data like you have indicated (like the id I mentioned in my previous post) you will not be able to get the data to display in the view the way you are wanting. You may have to look at the design of your tables.
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 10, 2011 at 9:36 am
Ok,
Then what changes I have to perform on table to get this output.
I have make diffrent two views and the combine them in the report.
But the report not showing the output can you tell me the code to display multiple view in crystal report.
Because when i use only one view then it display the data but for two its not working. we can display multiple table in a report as like can we display multiple views in report. if yes please can you post some code for that.
May 10, 2011 at 10:11 pm
please give me solution for this problem. How I can solve this.
May 11, 2011 at 12:27 am
Unfortunately I am not able to give you any advice around crystal reports. In my previous posts I have outlined to you that you need to have a column in your table like a customerid for example that can link your assets and liabilities for your customers like I showed in my example code. Unless you have something like this you can not link the data appropriately without doing cross joins and receiving multiple rows which I am assuming you are not wanting.
Thanks
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 13, 2011 at 11:32 am
can you provide me some links from which i can understand(learn) more about views. because i have to do this task. Its realy important for me.
May 13, 2011 at 7:24 pm
Prashant, your first point of call should be BOL. The underlying problem that you have is your underlying table structure. Unless you can fix your table structure creating a select statement which is used to create your view is not going to give you the results that you require.
My previous posts show an example of a table that I created. This had an ID column which allowed me to link the data. You need to find or have that common link in your data to be able to display appropriately.
Thanks
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 13, 2011 at 9:29 pm
prashantcomp90 (5/9/2011)
Hello,I am working on sql2005 and I want to create view for one table.
Table details :
table name : groups
feilds:
groupnm
debit
credit
and etc.
I want to create view with these three columns. I want to search on Assets and Liability. It should create six columns. three for assets,debit and credit and other three Liability,debit and credit.
I use the queary but it display only three columns and the data is combine I want to display it in seperate columns.
How can I perform this task.
Thanks.
Your table structure is not correct. Please generate the tables and indexes.
Feel free to add scripts to populate your tables.
Perhaps I'm overlooking something but you appear to be duplicating columns when you specify the result you expect to receive.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 16, 2011 at 12:02 am
as per your advice i have create new table Details are:
table name ledger
Feilds:
id
accnm
grpnm
supgrp
deb
crd
id is unique for each account name.
data in tahe table is :
id accnm grpnm supgrp deb crd
1. ICICI Bank Account Assets 10 10
2. SBI Bank Account Assets 20 20
3. loan loan Liability 30 30
4. crt lia lia Liability 40 40
Queary is like this :
SELECT l1.grpnm AS Assets, SUM(l1.deb) AS [Debit A], SUM(l1.crd) AS [Credit A], l2.grpnm AS Liability, SUM(l2.deb) AS [Debit L], SUM(l2.crd) AS [Credit L]
FROM dbo.ledger AS l1 INNER JOIN
dbo.ledger AS l2 ON l1.grpnm = l2.grpnm
WHERE (l1.supgrp = 'Assets') AND
(l2.supgrp = 'Liability')
GROUP BY l1.grpnm, l2.grpnm
but it display null output.
if i use OR then it display same in the both output. and also make duplication.
what is wrong. please correct it. and tell me my table is correct or not.
May 19, 2011 at 12:46 pm
Hello,
Sorry because i am forcing u to give reply.
I am sorry but I relay don’t know about sql views. and this work can not be done without view. Please guide me to solve this. as your advice i have create new table for that. Please don’t mind.
Please dont mind..
🙁
May 19, 2011 at 12:49 pm
prashantcomp90 (5/19/2011)
I am sorry but I relay don’t know about sql views. and this work can not be done without view.
No worries and no need to create a view, just use the base table(s).
Does that answer your question?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 12:56 pm
which base tables. do mean database tables. but from that how can i get this output. I use parameterized query for that but it wont work properly
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply