February 20, 2014 at 4:21 am
Hi,
First of all, i am new to sql. Here is the sample (for both table1 and table2, i have created a "SNO" as primary key and it's also identity column)
Table1
------
PID PNAME PartID
--- ----- ------
0 Length 1
1 Breadth 1
2 Height 1
0 Area 2
1 Volume 2
Table2
------
SampleID PID Pvalue PartID
-------- --- ------- ------
0 0 10 1
0 1 10 1
0 2 10 1
1 0 20 1
1 1 20 1
1 2 20 1
0 0 10 2
0 1 10 2
Depending upon the PartID, i must get the following results
PARTID: 1
SampleID Length Breadth Height
-------- ------ ------- ------
0 10 10 10
1 20 20 20
PARTID: 2
SampleID Area Volume
-------- ---- ------
0 10 10
How to achieve the desired output as mentioned above in SQL Server 2008?
Thanks,
Rajagopalan
February 20, 2014 at 9:21 am
As you're quite new with SQL and this site, I'm creating sample data in a way that we can just copy and execute it. You're expected to do this and many people won't help you if you don't.
Here's you're sample data:
CREATE TABLE #Table1(
PID int,
PNAME varchar(50),
PartID int)
INSERT #Table1 VALUES
(0, 'Length', 1),
(1, 'Breadth', 1),
(2, 'Height', 1),
(0, 'Area', 2),
(1, 'Volume', 2)
CREATE TABLE #Table2(
SampleID int,
PID int,
Pvalue int,
PartID int)
INSERT #Table2 VALUES
(0, 0, 10, 1),
(0, 1, 10, 1),
(0, 2, 10, 1),
(1, 0, 20, 1),
(1, 1, 20, 1),
(1, 2, 20, 1),
(0, 0, 10, 2),
(0, 1, 10, 2)
Next, you need something that is called PIVOT or CROSS TABS. You can find basic information on how to do it in this article: http://www.sqlservercentral.com/articles/T-SQL/63681/
This is the first part and you need to understand how does it work. For your sample data it would look like this:
DECLARE @PartID int
SET @PartID = 1
--Static Version
SELECT t2.SampleID,
MAX( CASE WHEN t1.PNAME = 'Length' THEN t2.Pvalue END) AS [Length],
MAX( CASE WHEN t1.PNAME = 'Breadth' THEN t2.Pvalue END) AS [Breadth],
MAX( CASE WHEN t1.PNAME = 'Height' THEN t2.Pvalue END) AS [Height],
MAX( CASE WHEN t1.PNAME = 'Area' THEN t2.Pvalue END) AS [Area],
MAX( CASE WHEN t1.PNAME = 'Volume' THEN t2.Pvalue END) AS [Volume]
FROM #Table1 t1
JOIN #Table2 t2 ON t1.PID = t2.PID AND t1.PartID = t2.PartID
WHERE t1.PartID = @PartID
GROUP BY t2.SampleID;
You ask for different columns according to your PartID, this is only achieved by dynamic code and it's explained on the second part of the article: http://www.sqlservercentral.com/articles/Crosstab/65048/
Be careful because if you don't work correctly, you might compromise your database. For more about this, read about SQL Injection.
Here's an example based on your sample data.
DECLARE @PartID int
SET @PartID = 2
DECLARE @SQL nvarchar(max);
WITH Names AS(
SELECT t1.PNAME
FROM #Table1 t1
--JOIN #Table2 t2 ON t1.PID = t2.PID AND t1.PartID = t2.PartID
WHERE t1.PartID = @PartID
GROUP BY t1.PNAME
)
SELECT @SQL = 'SELECT t2.SampleID ' + CHAR(10) +
CAST( (SELECT ',MAX( CASE WHEN t1.PNAME = ''' + n.PNAME + ''' THEN t2.Pvalue END) AS [' + n.PNAME + ']' + CHAR(10)
FROM Names n
FOR XML PATH('')) AS nvarchar(max)) +
'FROM #Table1 t1' + CHAR(10) +
'JOIN #Table2 t2 ON t1.PID = t2.PID AND t1.PartID = t2.PartID' + CHAR(10) +
'WHERE t1.PartID = @PartID ' + CHAR(10) +
'GROUP BY t2.SampleID;';
PRINT @SQL
EXEC sp_executesql @SQL, N'@PartID int', @PartID
Be sure to understand the solution before implementing it. Ask any questions that you need and read the articles.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply