Database

Klaus-Werner Konrad wrote:

that confused me

Select House, 'Sale', Sale as Value
from Table
union
Select House, 'Purchase', Purchase as Value
from Table

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;

A	Jan	Sale	        2000
A	Jan	Purchase	150
B	Jan	Sale	        1000
B	Jan	Purchase	200

SELECT a.*, b.*
FROM tblPersonName a LEF OUTER JOIN tblPersonName b
     ON a.lname = b.lname

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;

Roger Wright wrote:

use a single table date-meterID-HE_n value