need help with pivot/cross tab or matrix table

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

SQL Server 2005 Forums

Transact-SQL (2005)

need help with pivot/cross tab or matrix table

Author

Topic

Tart_SQL
Starting Member

41 Posts

Posted - 2008-03-18 : 16:27:28

Hi I need to transform this table below

QRT qt_yr TA AVG_MA AVG_MP TMP
--- ----- --- ------ ------ ---
3 3Q07 62 48.59 57.54 62
2 2Qo7 0 0.00 0.00 0
1 1Q08 396 20.96 43.54 396
4 4Q07 443 38.83 56.51 443

into this format.

A_YP 2Q07 3Q07 4Q07 1Q08
---- ---- ---- ---- ----
TA 0 62 396 443
AVG_MA 0 48.59 20.96 38.83
AVG_MP 0 57.54 43.54 56.51
TMP 0 62 396 443

Please help. Thanks.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-19 : 06:45:16

try this

DECLARE @T TABLE (QRT INT, qt_yr VARCHAR(50), TA INT, AVG_MA DECIMAL(18, 3), AVG_MP DECIMAL(18, 3), TMP INT)

INSERT INTO @T
SELECT 3, '3Q07', 62, 48.59, 57.54, 62 UNION ALL
SELECT 2, '2Qo7', 0, 0.00, 0.00, 0 UNION ALL
SELECT 1, '1Q08', 396, 20.96, 43.54, 396 UNION ALL
SELECT 4, '4Q07', 443, 38.83, 56.51, 443 UNION ALL
SELECT 5, '5Q06', 43, 38.3, 5.5, 4

--SELECT * FROM @T

SELECT * INTO #T2
FROM
(
SELECT QRT, qt_yr, TA, 'TA' AS 'Col' FROM @T
UNION ALL
SELECT QRT, qt_yr, AVG_MA, 'AVG_MA' FROM @T
UNION ALL
SELECT QRT, qt_yr, AVG_MP, 'AVG_MP' FROM @T
UNION ALL
SELECT QRT, qt_yr, TMP, 'TMP' FROM @T
) A

DECLARE @StrSql VARCHAR(MAX),
@Sql VARCHAR(MAX)
SELECT @StrSql = '',
@Sql = ''

SELECT @StrSql = @StrSql + ', MIN(CASE WHEN qt_yr = ''' + qt_yr + ''' THEN TA END) AS "' + qt_yr + '"'
FROM (SELECT DISTINCT QRT, qt_yr FROM #T2) A ORDER BY QRT DESC

--PRINT @StrSql

SELECT @Sql = @Sql + 'SELECT Col ' + @StrSql + ' FROM #T2 GROUP BY Col'

EXEC (@Sql)

DROP TABLE #T2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources