displaying data in acolumn wise using 3 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

displaying data in acolumn wise using 3 tables

Author

Topic

ramyasre
Starting Member

16 Posts

Posted - 2010-09-22 : 02:44:34

hi,

I am used PIVOT and Left Join in the following tables. But i am not get the actual output. kindly help me.

Table: Location

LocationID LocationName
_______________________

1000 Hyderabad
1002 Delhi
1004 Bombay

Table: Parameter

ParameterID ParameterName
_________________________

100 Total No. of Emp
101 Total No. of Houses

Table: Training_Parameter

ParameterID ParameterValue LocationID
_____________________________________

100 6222.56 1000
100 7899.09 1002
101 898.00 1002

These are 3 tables

I need the ouput like as follows

ParameterName Hyderabad Delhi Bombay
_________________________________________

Total No. of Emp 6222.56 7899.09 null
Total No. of Houses null 898.0 null

Regards

Tahnks

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 03:08:04

can you please provide us with your query?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-22 : 04:36:16

Name of citie are fixed ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

ramyasre
Starting Member

16 Posts

Posted - 2010-09-22 : 04:39:20

quote:
Originally posted by vaibhavktiwari83

Name of citie are fixed ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Cites are not fixed

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-22 : 04:40:56

If the name of cities and number of cities are fixed then try this -


SELECT ParameterName, [Hyderabad], [Delhi], [Bombay]
FROM 
(
SELECT ParameterName, ParameterValue, LocationName FROM Training_parameter T FULL OUTER JOIN Parameter P ON T.ParameterID = P.ParameterID 
FULL OUTER JOIN Location L ON T.LocationID = L.LocationID
WHERE ParameterName IS NOT NULL
) AS Src
PIVOT ( MAX( ParameterValue ) FOR LocationName IN ([Hyderabad], [Delhi], [Bombay])) AS Pvt

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

ramyasre
Starting Member

16 Posts

Posted - 2010-09-22 : 04:41:14

quote:
Originally posted by rohitvishwakarma

can you please provide us with your query?

I need the output like as follows

ParameterName Hyderabad Delhi Bombay
_________________________________________

Total No. of Emp 6222.56 7899.09 null
Total No. of Houses null 898.0 null

Thanks

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 06:51:25

Here is the final query handling any number of Locations
*Basically Improvisation on vaibhavktiwari83's query


CREATE TABLE #temp_columns
(
id INT IDENTITY(1,1),
columnNames VARCHAR(100)
)	
INSERT INTO #temp_columns
SELECT DISTINCT LocationName FROM Location

DECLARE @count INT
DECLARE @max_count INT

SET @count = 1
SET @max_count = (SELECT MAX(id) FROM #temp_columns)


DECLARE @sql_columns_list VARCHAR(1000)
SET @sql_columns_list = ' '

WHILE(@count <= @max_count) 
BEGIN
	DECLARE @column_name VARCHAR(100)
	SELECT @column_name = columnNames FROM #temp_columns WHERE id= @count
	IF(@count = @max_count)
	   SET @sql_columns_list = @sql_columns_list +'['+ @column_name + ']'
	ELSE
	   SET @sql_columns_list = @sql_columns_list +'['+ @column_name + ']'+ ' , '
	SET @count = @count + 1
END

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT ParameterName, '+@sql_columns_list+'
			FROM 
					(
						SELECT ParameterName, ParameterValue, LocationName 
						FROM Training_parameter T 
						FULL OUTER JOIN Parameter P ON T.ParameterID = P.ParameterID 
						FULL OUTER JOIN Location L ON T.LocationID = L.LocationID
						WHERE ParameterName IS NOT NULL
					) AS Src
			PIVOT ( MAX( ParameterValue ) FOR LocationName IN ('+@sql_columns_list+')) AS Pvt'

--PRINT @sql
EXEC(@sql)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-23 : 02:55:47

@rohitvishwakarma : You can make it more simple without using temp table and loop.

Have a look -


--SELECT DISTINCT LocationName FROM Location
DECLARE @sql_columns_list VARCHAR(1000)
SET @sql_columns_list = ' '
	SELECT @sql_columns_list = @sql_columns_list + '[' + LocationName + '],' 
	FROM 
	(
	SELECT DISTINCT LocationName FROM Location
	) A 
SELECT @sql_columns_list = LEFT(@sql_columns_list, LEN(@sql_columns_list)-1)

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT ParameterName, '+@sql_columns_list+'
			FROM 
					(
						SELECT ParameterName, ParameterValue, LocationName 
						FROM Training_parameter T 
						FULL OUTER JOIN Parameter P ON T.ParameterID = P.ParameterID 
						FULL OUTER JOIN Location L ON T.LocationID = L.LocationID
						WHERE ParameterName IS NOT NULL
					) AS Src
			PIVOT ( MAX( ParameterValue ) FOR LocationName IN ('+@sql_columns_list+')) AS Pvt'

--PRINT @sql
EXEC(@sql)

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 03:07:03

quote:
Originally posted by vaibhavktiwari83

@rohitvishwakarma : You can make it more simple without using temp table and loop.

Have a look -

DECLARE @sql_columns_list VARCHAR(1000)
SET @sql_columns_list = ' '
SELECT @sql_columns_list = @sql_columns_list + '[' + LocationName + '],'
FROM
(
SELECT DISTINCT LocationName FROM Location
) A
SELECT @sql_columns_list = LEFT(@sql_columns_list, LEN(@sql_columns_list)-1)

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Masterpiece

I was looking for something like that for weeks to avoid the loop and temp table, which were making my life difficult.

Thanks
Rohit

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-23 : 03:12:11

Thats my pleasure that I could be able to help you...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources