Crosstab without aggregate function

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)

Crosstab without aggregate function

Author

Topic

amolfarkade
Starting Member

6 Posts

Posted - 2008-02-06 : 18:12:27

How to get the crosstab out put for below tables

Table1

Column_ID Column_name
1 Column1
2 Column2
3 Column3
4 Column4
5 Column5

Table 2

Column_ID Row_ID Column_Containts
1 1 A1
2 1 A2
3 1 A3
4 1 A4
5 1 A5
1 2 B1
2 2 B2
3 2 B3
4 2 B4
5 2 B5
1 3 C1
2 3 C2
3 3 C3
4 3 C4
5 3 C5

I want the out put like below

Column1 Column2 Column3 Column4 Column5
A1 A2 A3 A4 A5
B1 B2 B3 B4 B5
C1 C2 C3 C4 C5

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 18:25:49

Using PIVOT like so:

SELECT  *
FROM    ( SELECT    t1.Column_name,
                    t2.Row_ID,
                    t2.Column_Containts
          FROM      Table_2 t2
                    JOIN Table_1 t1 ON t2.Column_ID = t1.Column_ID
        ) AS a PIVOT ( MAX(Column_Containts) FOR Column_name IN ( Column1, Column2, Column3, Column4, Column5 ) ) AS p

amolfarkade
Starting Member

6 Posts

Posted - 2008-02-06 : 18:28:39

Thanks for reply but the Column_names are not fixed every time.
Its dynamic grid, so always number of columns and rows changes.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-06 : 18:30:34

Without using aggregate function?
I don't think that is possible.

DECLARE	@Sample TABLE (ColumnID TINYINT, RowID TINYINT, CellValue CHAR(2))

INSERT	@Sample
SELECT	1, 1, 'A1' UNION ALL
SELECT	2, 1, 'A2' UNION ALL
SELECT	3, 1, 'A3' UNION ALL
SELECT	4, 1, 'A4' UNION ALL
SELECT	5, 1, 'A5' UNION ALL
SELECT	1, 2, 'B1' UNION ALL
SELECT	2, 2, 'B2' UNION ALL
SELECT	3, 2, 'B3' UNION ALL
SELECT	4, 2, 'B4' UNION ALL
SELECT	5, 2, 'B5' UNION ALL
SELECT	1, 3, 'C1' UNION ALL
SELECT	2, 3, 'C2' UNION ALL
SELECT	3, 3, 'C3' UNION ALL
SELECT	4, 3, 'C4' UNION ALL
SELECT	5, 3, 'C5'

SELECT		p.RowID,
		p.[1],
		p.[2],
		p.[3],
		p.[4],
		p.[5]
FROM		@Sample AS s
PIVOT		(
			MAX(s.CellValue)
			FOR s.ColumnID IN ([1], [2], [3], [4], [5])
		) AS p
ORDER BY	p.RowID

E 12°55'05.25"
N 56°04'39.16"

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 18:34:28

So youre after a dynamic script then:

DECLARE @Sql VARCHAR(MAX), @ColumnList VARCHAR(1000)

SELECT @ColumnList = COALESCE( @ColumnList + ', ' + Column_name, Column_name, @ColumnList )
FROM Table_1

SET @Sql = '
SELECT  *
FROM    ( SELECT    t1.Column_name,
                    t2.Row_ID,
                    t2.Column_Containts
          FROM      Table_2 t2
                    JOIN Table_1 t1 ON t2.Column_ID = t1.Column_ID
        ) AS a PIVOT ( MAX(Column_Containts) FOR Column_name IN ( '+ @ColumnList + ' ) ) AS p
'

EXEC (@Sql)

amolfarkade
Starting Member

6 Posts

Posted - 2008-02-06 : 18:50:15

jdaman,

its giving me below error
Incorrect syntax near the keyword 'Column1'.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 18:52:29

Try running this and let me know the output.


DECLARE @ColumnList VARCHAR(1000)

SELECT @ColumnList = COALESCE( @ColumnList + ', ' + Column_name, Column_name, @ColumnList )
FROM Table_1

PRINT @ColumnList

You should receive a result similar to: "Column1, Column2, Column3"

amolfarkade
Starting Member

6 Posts

Posted - 2008-02-06 : 20:03:13

John Daman,
Thanks a lot now it’s working very fine.

But now I want to reverse action from the PIVOT table data into two different tables.

Please help me

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-07 : 09:48:56

Lets start with the lay out for the two new tables using the sample data from your first post.

amolfarkade
Starting Member

6 Posts

Posted - 2008-02-07 : 13:09:21

John,

I need the data back from PIVOT table into below table as below

Column_ID Column_name
1 Column1
2 Column2
3 Column3
4 Column4
5 Column5

Table 2

Column_ID Row_ID Column_Containts
1 1 A1
2 1 A2
3 1 A3
4 1 A4
5 1 A5
1 2 B1
2 2 B2
3 2 B3
4 2 B4
5 2 B5
1 3 C1
2 3 C2
3 3 C3
4 3 C4
5 3 C5

amolfarkade
Starting Member

6 Posts

Posted - 2008-02-07 : 15:32:57

Any Update

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-07 : 16:45:46

Here is the complete code to pivot your tables and then unpivot the result back:

IF OBJECT_ID('tempdb..#Table_1') IS NOT NULL 
    DROP TABLE #Table_1
CREATE TABLE #Table_1 ( Column_ID INT, Column_name VARCHAR(7) )
INSERT #Table_1 ( Column_ID, Column_name )
SELECT 1, 'Column1' UNION
SELECT 2, 'Column2' UNION
SELECT 3, 'Column3' UNION
SELECT 4, 'Column4' UNION
SELECT 5, 'Column5'

IF OBJECT_ID('tempdb..#Table_2') IS NOT NULL 
    DROP TABLE #Table_2
CREATE TABLE #Table_2 ( Column_ID INT, Row_ID INT, Column_Containts VARCHAR(2) )
INSERT #Table_2 ( Column_ID, Row_ID, Column_Containts )
SELECT 1, 1, 'A1' UNION
SELECT 2, 1, 'A2' UNION
SELECT 3, 1, 'A3' UNION
SELECT 4, 1, 'A4' UNION
SELECT 5, 1, 'A5' UNION
SELECT 1, 2, 'B1' UNION
SELECT 2, 2, 'B2' UNION
SELECT 3, 2, 'B3' UNION
SELECT 4, 2, 'B4' UNION
SELECT 5, 2, 'B5' UNION
SELECT 1, 3, 'C1' UNION
SELECT 2, 3, 'C2' UNION
SELECT 3, 3, 'C3' UNION
SELECT 4, 3, 'C4' UNION
SELECT 5, 3, 'C5'

DECLARE @Sql VARCHAR(MAX), @ColumnList VARCHAR(1000)

SELECT @ColumnList = COALESCE( @ColumnList + ', ' + Column_name, Column_name, @ColumnList )
FROM #Table_1
/* PIVOT #Table_2 INTO ##pvt*/
SET @Sql = '
SELECT  * INTO ##pvt
FROM    ( SELECT    t1.Column_name,
                    t2.Row_ID,
                    t2.Column_Containts
          FROM      #Table_2 t2
                    JOIN #Table_1 t1 ON t2.Column_ID = t1.Column_ID
        ) AS a PIVOT ( MAX(Column_Containts) FOR Column_name IN ( '+ @ColumnList + ' ) ) AS p
'

IF OBJECT_ID('tempdb..##pvt') IS NOT NULL 
    DROP TABLE ##pvt

EXEC (@Sql)

SELECT  *
FROM    ##pvt

/* UNPIVOT ##pvt */
SET @Sql = '
SELECT  t1.Column_ID,
        Row_ID,
        Column_Containts
FROM    ( SELECT    Row_ID,
                    Column1,
                    Column2,
                    Column3,
                    Column4,
                    Column5
          FROM      ##pvt
        ) a UNPIVOT ( Column_Containts FOR Column_name IN ( ' + @ColumnList + ' ) ) unpvt
        JOIN #Table_1 t1 ON unpvt.Column_name = t1.Column_name
'

EXEC (@Sql)

Edit: I didnt recreate #Table_1 since it is only a lookup table and should still be available.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources