Select Data from multiple rows to one row

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

Select Data from multiple rows to one row

Author

Topic

raghu_nalumachu
Starting Member

2 Posts

Posted - 2008-02-27 : 01:45:45

Hi, I am new to to the usage of sql server.I have data in the below mentioned format in table called stock_transaction.


stocknumber  transtype     transsubtype     balance

s01           in              cust          100
s01           out             cust          200
s01           in              deal          300
s01           out             cust          100

s02           in               deal          200
s02           out             cust           300
s02           in              cust           100
s02           out             cust           200
s02           in             cust           300

I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like
(in,cust) (out,cust),(in,deal) as below where (in,cust) (out,cust),(in,deal) are temporary aliases only for displaying as shown below


stocknumber  in,deal   out,cust  in,cust     
s01             300      300      100
s02             200      500      400

I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 02:04:43

[code]SELECT stocknumber,
SUM(CASE WHEN transtype='in' AND transsubtype='deal' THEN balance ELSE 0 END) AS [in,deal],
SUM(CASE WHEN transtype='out' AND transsubtype='cust' THEN balance ELSE 0 END) AS [out,cust],
SUM(CASE WHEN transtype='in' AND transsubtype='cust' THEN balance ELSE 0 END) AS [in,cust]
FROM stock_transaction
GROUP BY stocknumber[/code]

raghu_nalumachu
Starting Member

2 Posts

Posted - 2008-02-27 : 02:18:52

thank you visakh the query is working fine>once agin thanks for your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-27 : 03:03:58

For more informations read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-28 : 00:27:13

hi,

try with this also

Declare @Temp TABLE (id varchar(100) , Type varchar(100), SubType Varchar(100), Amount Decimal(18,3))

INSERT INTO @Temp
SELECT 'S01' , 'In','Cust' ,100 UNION ALL
SELECT 'S01' , 'Out','Cust' ,200 UNION ALL
SELECT 'S01' , 'In','Deal', 300 UNION ALL
SELECT 'S01' , 'Out','Cust' ,100 UNION ALL
SELECT 'S02' , 'In' ,'Deal' ,200 UNION ALL
SELECT 'S02' , 'Out','Cust', 300 UNION ALL
SELECT 'S02' , 'In','Cust' , 100 UNION ALL
SELECT 'S02' , 'Out','Cust' , 200 UNION ALL
SELECT 'S02' , 'In' ,'Cust' , 300

SELECT Id, Type + ', ' + Subtype AS SubType, Amount INTO #Temp FROM @Temp

SELECT Id, SubType, Sum(Amount) AS Amount INTO #T
FROM #Temp GROUP BY SubType, Id

Declare @Sql Varchar(8000)
Set @sql = ''
DEclare @str Varchar(8000)
Set @str = ''

Select @sql = @sql + ', Min(Case when SubType = ''' +SubType + ''' Then Amount End ) AS "' + SubType + '"'
From (Select DISTINCT SubType From #T )a

--Select @sql

Select @str = @str + 'Select Id '+@sql+' From #T Group By Id'
--print @str
Exec (@str)

DROP TABLE #Temp, #T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 00:31:49

quote:
Originally posted by ranganath

hi,

try with this also

Declare @Temp TABLE (id varchar(100) , Type varchar(100), SubType Varchar(100), Amount Decimal(18,3))

INSERT INTO @Temp
SELECT 'S01' , 'In','Cust' ,100 UNION ALL
SELECT 'S01' , 'Out','Cust' ,200 UNION ALL
SELECT 'S01' , 'In','Deal', 300 UNION ALL
SELECT 'S01' , 'Out','Cust' ,100 UNION ALL
SELECT 'S02' , 'In' ,'Deal' ,200 UNION ALL
SELECT 'S02' , 'Out','Cust', 300 UNION ALL
SELECT 'S02' , 'In','Cust' , 100 UNION ALL
SELECT 'S02' , 'Out','Cust' , 200 UNION ALL
SELECT 'S02' , 'In' ,'Cust' , 300

SELECT Id, Type + ', ' + Subtype AS SubType, Amount INTO #Temp FROM @Temp

SELECT Id, SubType, Sum(Amount) AS Amount INTO #T
FROM #Temp GROUP BY SubType, Id

Declare @Sql Varchar(8000)
Set @sql = ''
DEclare @str Varchar(8000)
Set @str = ''

Select @sql = @sql + ', Min(Case when SubType = ''' +SubType + ''' Then Amount End ) AS "' + SubType + '"'
From (Select DISTINCT SubType From #T )a

--Select @sql

Select @str = @str + 'Select Id '+@sql+' From #T Group By Id'
--print @str
Exec (@str)

DROP TABLE #Temp, #T

Is there a necessity to use dynamic sql for this?

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-28 : 01:10:08

Hi
Visak

if we dont know how many rows in that table then use Dynamic
otherwise Static

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 01:16:58

quote:
Originally posted by ranganath

Hi
Visak

if we dont know how many rows in that table then use Dynamic
otherwise Static

ok i see your point.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources