Database

CREATE TABLE #CM_Master
(
	ShiftID INT, ShiftName VARCHAR(50)
)

INSERT INTO #CM_Master
SELECT 1, 'Day' UNION
SELECT 2, 'Night'
 


CREATE TABLE #CM_Detail
(
	CM_ID INT, Site_ID INT, Level_ID INT, ShiftID INT, Number INT
)

INSERT INTO #CM_Detail
SELECT 1, 1, 1, 1, 5 UNION
SELECT 2, 1, 2, 1, 4 UNION
SELECT 3, 1, 1, 2, 2 UNION
SELECT 4, 2, 2, 2, 8 



--SELECT * FROM #CM_Detail

SELECT DISTINCT CD.Site_ID, CD.Level_ID, ISNULL(A.Day,0) AS [Day], ISNULL(B.Night,0) AS Night
FROM #CM_Detail CD
LEFT JOIN
(
SELECT Site_ID, Level_ID, 
SUM(Number) [Day]
FROM #CM_Detail
WHERE ShiftID = 1
GROUP BY Site_ID, Level_ID
) A ON CD.Site_ID = A.Site_ID AND CD.Level_ID = A.Level_ID
LEFT JOIN 
(
SELECT Site_ID, Level_ID, 
SUM(Number) AS [Night]
FROM #CM_Detail
WHERE ShiftID = 2
GROUP BY Site_ID, Level_ID
) B ON CD.Site_ID = B.Site_ID AND CD.Level_ID = B.Level_ID

DECLARE @PivotColumnHeader VARCHAR(MAX)
SELECT @PivotColumnHeader = COALESCE(@PivotColumnHeader + ', [' + ShiftName + ']', '[' + ShiftName + ']')
FROM #CM_Master


DECLARE @SQL VARCHAR(MAX)
SET @SQL = N'SELECT * FROM
(
SELECT Site_ID, Level_ID, ShiftName, ISNULL(Number,0) AS Number  FROM #CM_Detail CD
INNER JOIN #CM_Master CM ON CD.ShiftID = CM.ShiftID
) P
PIVOT
(
    SUM(Number) FOR ShiftName IN (' + @PivotColumnHeader + ')
) AS PivotTable'

EXECUTE (@SQL)

Robymon wrote:

Is it possible to

Bastard Programmer from Hell
if you can't read my code, try converting it here[^]

select p.* ,e.*,s.*,jh.*
from persons p
inner join Education e on e.PersonID  = p.PersonID
inner join Skills s on s.PersonID  = p.PersonID
inner join JobHistory jh on jh.PersonID  = p.PersonID

Education = Masters
Skills = Computer Networking 
Experience = 6 years

Select TblA.*,TblB.*,TblC.*,TblD.* from persons TblA
inner join Education TblB on TblB.PersonID  = TblA.PersonID
inner join Skills TblC on TblC.PersonID  = TblA.PersonID
inner join JobHistory TblD on TblD.PersonID  = TblA.PersonID

C:\Projects\KCDXlogger>dbt kcdx "describe Artist ; describe Title ; describe Playlist
DBT  V6.0 -- Simple SQL interface                  Sir John E. Boucher 2003

describe Artist

Name Type1         Type2                Nullable Unique Read only
---- ------------- -------------------- -------- ------ ---------
Id   System.Guid   DBTYPE_GUID          True     False  False
Name System.String DBTYPE_WVARCHAR(255) True     False  False


2 records affected.


describe Title

Name Type1         Type2                Nullable Unique Read only
---- ------------- -------------------- -------- ------ ---------
Id   System.Guid   DBTYPE_GUID          True     False  False
Name System.String DBTYPE_WVARCHAR(255) True     False  False


2 records affected.


describe Playlist

Name     Type1           Type2                Nullable Unique Read only
-------- --------------- -------------------- -------- ------ ---------
CreTim   System.DateTime DBTYPE_DATE          True     False  False
Text     System.String   DBTYPE_WVARCHAR(255) True     False  False
PlaTim   System.DateTime DBTYPE_DATE          True     False  False
ArtistId System.Guid     DBTYPE_GUID          True     False  False
TitleId  System.Guid     DBTYPE_GUID          True     False  False


5 records affected.

PIEBALDconsult wrote:

Yes, which is very limiting, no?

Shameel wrote:

you can't create a song before you have singers.

Bastard Programmer from Hell
if you can't read my code, try converting it here[^]

Eddy Vluggen wrote:

Do keep up the good work and keep answering questions; not only will it have saved a few people quite some time and frustration, it's also a very efficient way of learning.

Shameel wrote:

the singer is the writer

cmd.Parameters.AddWithValue("?description", txtdesc.Text);