Make row data as column - SQL Server Forums

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

Make row data as column

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-08-04 : 21:22:49

Hai Everyone,
I have a table with data like this

Tran_no Description Debit Credit
1 SW 1000
1 BR1 500
1 BR2 200
1 BR3 300
2 SQ 500
2 BW1 200
2 BW2 200
2 BW3 100

Now what I wanna do this that
Tran_no Val1 Val2 Val3 Val4
1 1000 500 200 300
2 500 200 200 100

Thanks in advance
Nirene

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-05 : 03:56:31

1) Search for PIVOT/CROSS TAB REPORT queries
2) The SQL Server 2005 and later built-in PIVOT keyword

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-06 : 02:26:57

or

Read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-08-06 : 03:38:47

Hai,
Where to search for Cross Tab Report Query.

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-08-06 : 03:39:52

Im using SQL Server 2000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-06 : 04:06:16

quote:
Originally posted by nirene

Hai,
Where to search for Cross Tab Report Query.

In SQL Server help file

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-06 : 04:15:25

or try this
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Madhivanan

Failing to plan is Planning to fail

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-08-07 : 09:06:57

Hai,
Can you give a select query statement to do this

Cos it is very urgent for my project.

Nirene

sbalaji
Starting Member

48 Posts

Posted - 2007-08-08 : 06:08:53

you can try this
http://www.sql.co.il/ug/16/Dynamic%20Crosstab.txt.

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-08-08 : 06:41:19

Hello Balaji,
The url doesnt work,could you just give the content.

Nirene

sbalaji
Starting Member

48 Posts

Posted - 2007-08-09 : 01:45:56

i think u r realy having a bad time.

CREATE PROC sp_CrossTab
@table AS sysname, -- table to crosstab
@onrows AS nvarchar(128), -- grouping key values (on rows)
-- can be column or computation,
-- e.g., CustomerID, MONTH(OrderDate)

@onrowsalias AS sysname = NULL, -- alias for grouping column,
-- NULL = use original col name

@oncols AS nvarchar(128), -- destination columns (on columns),
-- can be column or computation,
-- e.g., EmployeeID, YEAR(OrderDate)

@sumcol AS sysname = NULL -- Data cells,
-- NULL = count rows,
-- <value> = column to sum
AS

DECLARE
@sql AS varchar(8000),
@NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

-- step 1: beginning of SQL string
--
-- start generating the SQL string,
-- starting with the grouping key as the first column.
-- use an alias for that column, if one was provided
SET @sql =
'SELECT' + @NEWLINE +
' ' + @onrows +
CASE
WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
ELSE ''
END

-- step 2: storing keys in a temp table
--
-- generate another SELECT statement
-- which stores the unique values of the
-- grouping column/ computation in a temp table
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
'FROM ' + @table

EXEC (@keyssql)

-- step 3: middle part of SQL string
--
-- concatenate each key value as a new column
-- based on the unique keys of
-- the grouping column/computations which were
-- stored in the temp table #keys
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
SET @sql = @sql + ',' + @NEWLINE +
' SUM(CASE CAST(' + @oncols +
' AS nvarchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
WHEN @sumcol IS NULL THEN '1'
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS c' + @key

SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END

-- step 4: end of SQL string
--
-- concatenate the last part of the SQL string
-- including the FROM clause
-- and the grouping column/computation
SET @sql = @sql + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @onrows + @NEWLINE +
'ORDER BY ' + @onrows

-- PRINT @sql + @NEWLINE -- For debug
EXEC (@sql)
GO

--Courtesy:http://www.sql.co.il/ug/16/Dynamic%20Crosstab.txt.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources