|
Seriously - you do not have BOL or google where you are!
Select House, 'Sale', Sale as Value
from Table
union
Select House, 'Purchase', Purchase as Value
from Table
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
You can use a union, as already suggested, and with SQL Server you can also use the UNPIVOT command.
WITH myData(Store , Mth , Sale , Purchase )
AS
(
SELECT 'A','Jan', 2000,150
UNION SELECT 'B','Jan', 1000,200
)
SELECT Store, Mth, Expense, Value
FROM
(
Select Store, Mth, Sale, Purchase
FROM myData
) p
UNPIVOT
( Value FOR Expense IN (Sale, Purchase) ) AS unpvt;
Let me explain that a bit. The first bit (Starting 'WITH') just gives us some example data, I have added a second row onto your original data to help confirm the result.
The second bit (Starting 'SELECT') Selects the data from an unpivoted view of your SALE and Purchase columns. The result of the above query is
A Jan Sale 2000
A Jan Purchase 150
B Jan Sale 1000
B Jan Purchase 200
|
|
|
|
|
Hi,
How do I write the SQL code to select data from these tables:
tblPerson
ID DOB
1 01/01/2010
2 01/02/2011
3 10/01/2009
tblPersonName
ID FName MName LName NameType
1 James D Doe L
1 Jim (null) Doe C
2 Martha (null) Stu L
3 William H Jefferson L
3 Bill (null) Jefferson
to look like this (flattened)?
ID FName_Legal MName_Legal LName_Legal FName_Common MName_Common LName_Common
1 James D Doe Jim (null) Doe
2 Martha (null) Stu (null) (null) (null)
3 William H Jefferson Bill (null) Jefferson
Thanks
|
|
|
|
|
The column headers seem like you want to select the person with the same first, middle OR last name but your results seem like you would select only based on the same last name. However, basically you would use self join. If the last name is used to match rows, it could be something like:
SELECT a.*, b.*
FROM tblPersonName a LEF OUTER JOIN tblPersonName b
ON a.lname = b.lname
This query has problems though (depending on your specs). If you have three persons with the same last name you get those on multiple rows (each pair). Also each pair will be listed twice (both ways), but I'm not able to say if that's what you want.
Also the ID field seems quite suspicious. Do you really have several rows with the same id? If not, that could be used to eliminate the pairs from being listed twice (again if that's what you want).
|
|
|
|
|
Hi Mika,
Sorry my example wasn't clear.. ID is the PK for tblPerson and a FK in tblPersonName. The join will be done using the ID field. tblPersonName contains a table of all the different names a person has e.g. Legal name, common name etc
What I want to do is to be able to list all the different names of a person in one row as opposed to multiple rows.
Thanks
|
|
|
|
|
|
|
|
CREATE TABLE #Person
(
ID INT NOT NULL,
DOB DATE NOT NULL
);
CREATE TABLE #PersonName
(
ID INT NOT NULL,
PID INT NOT NULL,
FNAME VARCHAR(50) NOT NULL,
MNAME VARCHAR(10) NULL,
LNAME VARCHAR(50) NOT NULL,
NAMETYPE CHAR(1) NOT NULL
);
INSERT INTO #Person
(ID, DOB)
VALUES
(1, '2010-01-01'),
(2, '2011-02-01'),
(3, '2009-01-10');
INSERT INTO #PersonName
(ID, PID, FNAME, MNAME, LNAME, NAMETYPE)
VALUES
(1, 1, 'James', 'D', 'Doe', 'L'),
(2, 1, 'Jim', NULL, 'Doe', 'C'),
(3, 2, 'Martha', NULL, 'Stu', 'L'),
(4, 3, 'William', 'H', 'Jefferson', 'L'),
(5, 3, 'Bill', NULL, 'Jefferson', 'C');
WITH L AS
(
SELECT *
FROM #PersonName
WHERE NAMETYPE = 'L'
),
C AS
(
SELECT *
FROM #PersonName
WHERE NAMETYPE = 'C'
)
SELECT P.ID,
L.FNAME AS FNAME_L,
L.MNAME AS MNAME_L,
L.LNAME AS LNAME_L,
C.FNAME AS FNAME_C,
C.MNAME AS MNAME_C,
C.LNAME AS LNAME_C
FROM #Person P
LEFT JOIN L
ON L.PID = P.ID
LEFT JOIN C
ON C.PID = P.ID;
DROP TABLE #PersonName;
DROP TABLE #Person;
|
|
|
|
|
|
Hi,
I want to create a report with 3 different datasets (dataset 1, 2 and 3) from a single datasource. The report layout will be a 3 section for every single record found in dataset1. Dataset 2 and dataset 3 will get the ID from dataset1 and display the 2nd and 3rd section of the report. Please help me how to create this kind of report.
Thanks
|
|
|
|
|
Are you talking about Gupta Report Builder ?
|
|
|
|
|
Hi,
I would like to install the sql server 2005 express on my machine.
I have found a download for it but it does not tell me if it includes the management studio (The window where I can see databases, tables, etc...)
Is there another download for just the management studio of the sql; server 2005 express?
Thanks
|
|
|
|
|
|
I'm trying to monitor electric meter readings for several substations, and provide a database that can be used to analyze usage patterns. The meter readings are provided in csv format with the structure:
Date, MeterID, HE1, HE2, HE3,..., HE24, OffPeak, OnPeak, Total
where HEn is the hour ending MWh usage; I don't much care about the On/Off Peak values, or the totals.
I want to store the data in the form:
Date MeterID1 MeterID2 MeterID3 MeterID4
date HE1 HE1 HE1 HE1
date HE2 HE2 HE2 HE2
date ... ... ... ...
date HE24 HE24 HE24 HE24
That's one approach, at least. But it creates a problem if we add another meter to the mix later. I'd have to recreate the table with a new schema, then repopulate it from the previous table.
Another way would be to create a separate table for each meter, using the same schema; that would make it easier to add new meters later with no manual operations, but it seems inefficient to me.
Worse still would be to use a single table date-meterID-HEn value. That would entail a huge number of records with little content, hardly a useful structure.
I'm sure this sort of problem happens all the time in other applications, and there must be some recommended solution, but I don't know of it. Can someone with more experience suggest a solution?
Will Rogers never met me.
|
|
|
|
|
Roger Wright wrote: use a single table date-meterID-HEn value
Thats actually the way to go. The amount of rows isn't bothering tha database very much if you index the table properly.
It makes it very easy to add a new meter, or get the values for a single meter for a certain datespan.
If you want to show values for several meters at the same time you can PIVOT the data.
You should probably also add a separate table with info on the meters.
|
|
|
|
|
Yup what Jorgen said, definately go for a 2 table design.
MeterTable - with any details about the meter if there is more than just a name
TranTable - 1 record = a meter/hour read.
And yes a pivot view (even if it is hard coded to #meters) is an excellent option.
If you use the first 2 option you are committing database abuse. Use option 3 (your perceived worst option) this is by fard the best design.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Roger Wright wrote: Worse still would be to use a single table date-meterID-HEn value.
That would entail a huge number of records with little content, hardly a useful
structure.
Actually it's quite a common design. Modern databases won't have much of a problem with this, particularly since it seems as if you will be doing mostly reads, inserts occurring in batches relatively infrequently, few or possibly even no updates to existing rows. If you find the table grows too big over time, you can look to archive data off (e.g. data over a year old could be moved out to another table). There is another thread here about archiving.
|
|
|
|
|
I agree with what the others have said; use a pivot.
Where I work, we made a number of products on any given day and we need to report metrics on the products on a daily basis.
The corporate solution was a spreadsheet limited to 20 products; new products are simply added to a column and grouped as a 'family' of products even though the specs aren't the same... just a 'close enough' fit.
I developed a solution to pull the data into a single table, as others have suggested, and used a pivot to show the products by day with metrics. It was some work to get it going, since it was my first stab at using the pivot, but it has been wonderful for reporting purposes.
Tim
|
|
|
|
|
Basically nothing new to add but since you've asked for opinions...
Having two tables is absolutely, definitely and undeniably the correct way because of:
- maintainability
- (quite) easy to query regardless of the reporting needs
- flexibility when quering
- efficiency
Depending on the needs I would even concider having three separate (typed) rows, one for On peak value, second for off peak and third for the total (if it cannot be calculated from the data). If the total can be calculated from the data I wouldn't store it, at least not in these tables.
Best regards,
mika
|
|
|
|
|
Hello,
Is there some tool in sql server which would help me compare two tables and tell me what is different in it?
Thanks!
|
|
|
|
|
Do you want to compare the contents of the tables. If that's the case I think the quickest way could be to use EXCEPT[^].
If the table structure is the same, you could do comparison like:
SELECT * FROM FirstTable
EXCEPT
SELECT * FROM SecondTable
and then other way round:
SELECT * FROM SecondTable
EXCEPT
SELECT * FROM FirstTable
If there are structural differences, specify the columns you want to compare.
|
|
|
|
|
Are you talking about comparing the structure or the data? They require 2 different strategies.
Do you want to do this for 1 pair of table or are you looking to compare an entire database? They require 2 different strategies.
If you need to compare databases then look at Red-Gate sql and data compare tools.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
To be more clear, I would like compare the data with in two tables of same design. Would like to report all rows that are different.
|
|
|
|
|