Database

Uma Shankar Patel wrote:

I tried google but there is no suitable answers. Please reply me.

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

Uma Shankar Patel wrote:

Yes I tried this,

Uma Shankar Patel wrote:

does Hosting provide provides Linked server facility?

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

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