|
I have designed my database with a one to many relationship where one user can have many statistics, but now I need to display the data in a single row format.
The UserStatistics table has a foreign key to the User table (user_id in UserStatistics)
Statistics might be height, weight, etc...
The table should look like this:
Name | Height | Weight
Joe 6' 150
And so on. The header will always be the same (Name, Height, Weight or whatever) but any time the user does not have that particular statistic I need a blank column. Do I need to rejoin against the UserStatistics table over and over again to get the results I need or what?
|
|
|
|
|
Assuming your statistics table looks like this:
| userid | property | value | | 1 | height | 180 | | 1 | width | 100 | | 2 | height | 170 | | 2 | width | 110 |
This query:
Select userid
,max(case when property = 'height' then value else null end) as height
,max(case when property = 'width' then value else null end) as width
from properties
group by useridWould get this result:
| userid | height | width | | 1 | 180 | 100 | | 2 | 170 | 110 |
Depending on what database you're using, the pivot function might already be included and in that case it's easy enough to google how to use it.
|
|
|
|
|
This is MySql. Thank you for the suggestion, between you and Mycroft I will get it sorted out either using pivot or when ... then.
Cheers, --EA
|
|
|
|
|
MySQL doesn't have a specific PIVOT functionality AFAIK.
|
|
|
|
|
It does not have a native pivot feature, but your aggregate / group by solution will work perfectly.
Cheers, --EA
|
|
|
|
|
Alternatively you can use a pivot [^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
eddieangel wrote: rejoin against the UserStatistics table over and over again
That's what I would do.
|
|
|
|
|
You can do this by using simple Join on these two tables..
Taking selected column in SELECT statement..
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
How to export query results into text file ?
|
|
|
|
|
For That You Have To Activate Few Services In SQL Server ::
Execute This Command Set;
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
Now To Get Output of any SQL query in a text file write this query:
EXEC xp_cmdshell 'bcp "YOUR QUERY HERE" queryout "Location Of TextFile Here" -T -c -t,'
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
|
Thanks For Reply .
I have use this code :
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC xp_cmdshell 'bcp "SELECT * FROM Silver.tblGroups" queryout "C:\bcptest2.txt" -T -S ALIJOONASUS\wintapsqlexpress -c -t,'
But i wanna to have <big>UTF-8</big> text file.
and Secondly :
How to have <big>Columns Name</big> also
modified 16-May-12 4:30am.
|
|
|
|
|
we have written program in C#.
now when we change the database(in sql express 2008) sp OR view OR add new table OR delete table OR ....
how can update the old customer DB within the newest one without changing the data in customer DB.
in other word :
the customers have previous application and database (full with their specific data). Now a new version is ready and the customer gets the update. In the mean time we made some modification on DB (new table, columns, maybe an old column deleted, or whatever). I’m pretty new in Linq and also SQL databases and my first solution can be: I check the applications/databases version and implement all the changes step by step comparing all tables, columns, keys, constrains, etc. (all this new information I have in my dbml and the old I asked from the existing DB). And I’ll do this each time the version changed. But somehow I feel, this is NOT a smart solution so I look for a general solution of this problem.
modified 16-May-12 4:30am.
|
|
|
|
|
jojoba2011 wrote: delete table
combined with
jojoba2011 wrote: without changing the data in customer DB
Seems like two opposing requirements. You need to review this.
Usually when schema updates are deployed to an exisiting database, ALTER becomes your friend. But each change needs to be analyzed and understood for it's impact. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
thanks for reply!
but i wanna to update my database (it should compare the old database with the Newest one and update the old one ...if need Add/Update/Delete SP,View,table,schema,...)
in other word :
the customers have previous application and database (full with their specific data). Now a new version is ready and the customer gets the update. In the mean time we made some modification on DB (new table, columns, maybe an old column deleted, or whatever). I’m pretty new in Linq and also SQL databases and my first solution can be: I check the applications/databases version and implement all the changes step by step comparing all tables, columns, keys, constrains, etc. (all this new information I have in my dbml and the old I asked from the existing DB). And I’ll do this each time the version changed. But somehow I feel, this is NOT a smart solution so I look for a general solution of this problem.
modified 15-May-12 23:05pm.
|
|
|
|
|
I host a website in public domain with ASP.Net and MS Sql Server.I want to update website database as my company oracle ERP databse updated.
Is it possible?
|
|
|
|
|
Yes using replication or possible triggers...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I dont need whole data in sql server ,i only need some tables.
so please tell me
how to replicate local oracle database to sql server(hosted in public domain).....
|
|
|
|
|
Not my 1 vote but you deserve it.
Take a look at the subject of replication, there are books on the subject, your question cannot be answered by a forum post.
Triggers spit can be written in the oracle DB that will write the data to SQL Server.
You REALLY need to look into your design as this seems to be dramatically flawed.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok,
My confusion not in replication,
Please see the post again.
I try to eleborate more,
My company using Oracle ERP.
Now i have to develop a customer portal in asp.net and sql server that is hosted by any hosting provider.
then
how I develop database is sql server(some tables of erp database) so that the customer data upadated if erp data upadated in real time.
Is it possible?
|
|
|
|
|
You could develop a small app which can be run as a scheduled task every minute or so which will capture data changed from Oracle and transfer to SQL Server. You will need something on Oracle which will place the data in a temporary storage area (probably a table) eg trigger or addition to stored procedures etc. This could be as simple as creating an insert or update statement etc. This will be captured by the app and executed on SQL Server.
|
|
|
|
|
In SQL Server you can create column descriptions. Where do you set the Table Description?
[UPDATE]
Ok, I figured out that you have to use an extended property. What I can't figure out is how to get back a list of tables that have extended properties set for them.
Anyone?
Thanks
If it's not broken, fix it until it is
modified 14-May-12 18:29pm.
|
|
|
|
|
This[^] might be of use.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Thanks. I also found this[^]
If it's not broken, fix it until it is
|
|
|
|
|
In a PRG file, a character array is declared as:
char(1) myvar[11]
and is initialized like:
myvar[1] = Chr(32)
myvar[2] = Chr(45)
...
myvar[10] = Chr(124)
My question is, since myvar[11] was never assigned a value, what value would I get?
I don't have access to a compiler/interpreter, only the source code.
Thanks.
- DC
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
