July 23, 2008 at 9:00 am
Hi,
i want to show the column data in row wise, can any one please help me how to write a query for this?
assume data in table like below:
Name Month Amount
------ ------ -------
ABC Jun-08 100
XYZ Feb-08 150
AAA Mar-08 200
ABC Feb-08 200
XYZ Mar-08 100
The output would be lie this:
Name Jun-08 Feb-08 Mar-08
----- ------- ------- -------
ABC 100 200 -
XYZ - 150 100
AAA - 200 -
Please help me the query for the above.
Thank you.
July 24, 2008 at 1:59 am
You can try the PIVOT operator, but you have to specify the columns that it will pivot on, so it is not really dynamic...
To get test data:
CREATE TABLE sales(name nvarchar(20) , month nvarchar(20) , amount int )
GO
INSERT INTO sales
SELECT
'ABC', 'Jun-08', 100
UNION ALL
SELECT
'XYZ', 'Feb-08', 150
UNION ALL
SELECT
'AAA', 'Mar-08', 200
UNION ALL
SELECT
'ABC', 'Feb-08', 200
UNION ALL
SELECT
'XYZ', 'Mar-08' , 100
--THEN:
SELECT
*
FROM
(SELECT name, month, Amount FROM sales ) S
PIVOT (SUM(amount) FOR month IN ([Feb-08],[Mar-08], [Jun-08])) AS PIVOTCOLUMNS
ORDER BY name
--OR
SELECT
PIVOTCOLUMNS.name ,
PIVOTCOLUMNS.[Feb-08],
PIVOTCOLUMNS.[Mar-08],
PIVOTCOLUMNS.[Jun-08]
FROM sales S
PIVOT (SUM(amount) FOR month IN ([Feb-08],[Mar-08], [Jun-08])) AS PIVOTCOLUMNS
ORDER BY
PIVOTCOLUMNS.name
July 24, 2008 at 3:54 am
Many thanks for your kind reply to my query..
but.. please advise me, how to proceed if i have N number of records in my table:
Name Month Amount
------ ------ -------
ABC Jun-08 100
XYZ Feb-08 150
AAA Mar-08 200
ABC Feb-08 200
XYZ Mar-08 100
. . .
. . .
. . .
. . .
N N N
Thank you once again.:)
July 24, 2008 at 4:03 am
i mean to say that.. month will not change (Jun-08,Feb-08,Mar-08) but other details Name and amount would be N times.
Name Month Amount
------ ------ -------
ABC Jun-08 100
XYZ Feb-08 150
AAA Mar-08 200
ABC Feb-08 200
XYZ Mar-08 100
. . .
. . .
. . .
. . .
N N N
July 24, 2008 at 4:45 am
The following works for me. It uses dynamic sql: (I am all for a simpler solution if anybody else has one!)
DECLARE @MONTHS NVARCHAR(MAX)
SELECT @MONTHS = ''
SELECT @MONTHS = @MONTHS + '[' + month + '],'
FROM (SELECT DISTINCT month FROM sales) S
SELECT @MONTHS = SUBSTRING(@MONTHS,1,LEN(@MONTHS) - 1 )
DECLARE @SQLSTR NVARCHAR(MAX)
SELECT @SQLSTR =
'SELECT
PIVOTCOLUMNS.*
FROM sales S
PIVOT (SUM(amount) FOR month IN (' + @MONTHS + ')) AS PIVOTCOLUMNS
ORDER BY
PIVOTCOLUMNS.name'
exec sp_executesql @SQLSTR
July 24, 2008 at 4:53 am
Also asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107310
N 56°04'39.16"
E 12°55'05.25"
July 24, 2008 at 8:12 am
I always recommend doing pivot operations in a front-end application, like Excel or Reporting Services, instead of in SQL. They do it better, more easily, and faster.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2008 at 10:39 am
[p]Ooh Fun. A pivot! [/p]
[p]This is the old-fashioned way I usually do it. I kept your date order just in case you needed it that way. The code looks slightly horrid but it is dead fast. I did the totals just like you'd see in Excel just to show off.[/p]
[p] I know from experience that GSquared is always right, but where you are having to aggregate a million or more rows to create a pivot report, I'd have thought SQL starts to get the yellow jersey. It would be interesting to test it out.[/p]
[font="Courier New"]
CREATE TABLE sales([name] NVARCHAR(20) ,
MONTH NVARCHAR(20) ,
amount INT )
GO
INSERT INTO sales
SELECT 'ABC', 'Jun-08', 100
UNION ALL SELECT 'XYZ', 'Feb-08', 150
UNION ALL SELECT 'AAA', 'Mar-08', 200
UNION ALL SELECT 'ABC', 'Feb-08', 200
UNION ALL SELECT 'XYZ', 'Mar-08' , 100
UNION ALL SELECT 'ABA', 'Jun-08', 200
UNION ALL SELECT 'ACC', 'Feb-08', 200
UNION ALL SELECT 'XXX', 'Mar-08' , 100
SELECT [name] =COALESCE([name],'Sum'),
[Jun-08]=SUM(CASE WHEN MONTH='Jun-08'
THEN amount ELSE 0 END),
[Feb-08]=SUM(CASE WHEN MONTH='Feb-08'
THEN amount ELSE 0 END),
[Mar-08]=SUM(CASE WHEN MONTH='Mar-08'
THEN amount ELSE 0 END),
[total]=SUM(amount)
FROM sales GROUP BY [name] WITH ROLLUP
ORDER BY GROUPING([name]),name
/*
result
name Jun-08 Feb-08 Mar-08 total
-------------------- ----------- ----------- ----------- -----------
AAA 0 0 200 200
ABA 200 0 0 200
ABC 100 200 0 300
ACC 0 200 0 200
XXX 0 0 100 100
XYZ 0 150 100 250
Sum 300 550 400 1250
(7 row(s) affected)*/
[/font]
Best wishes,
Phil Factor
July 26, 2008 at 1:39 am
thanks for your query and definitely its useful. BUT we can insert one by one record using union all to the created table. can we put bulk records instead inserting each record ? i mean can we make all columns arrays to insert all records in the table.
July 26, 2008 at 2:09 am
Sorry, I'm not sure if I understand the question. The UNION ALL statements were just there to insert the sample data into the test table. You'd use all sorts of techniques to get the data into the table: it wouldn't matter.
Best wishes,
Phil Factor
July 28, 2008 at 12:01 pm
Phil, here's why I recommend pivoting data outside of SQL.
Using your sample, I inserted 1-million rows of data into the same table structure you used.
Then I added three more months of data.
Your query, as written, was faster than Excel when it came to querying the data. Four seconds faster. But, with more months, it had to be rewritten to accomodate the extra columns.
So I added the extra columns to the query. Still runs quite fast (approx 1 second). Excel took about 5 seconds, but I didn't have to rewrite anything. Just clicked the "refresh data" button on the toolbar.
So I added another 3 months, which takes it into next year. Had to rewrite the query again. It's a little slower, takes just over 1 second now, on 1.5-million rows of data. Excel still taking about 5 seconds, but again, no code rewrite.
It's not the speed. SQL is faster. But we're not talking about a difference of hours or even minutes here: it's a 3-4 second advantage.
It's having to rewrite the query every month that I object to.
And what happens when someone goes, "This darn query is 200 columns wide. I just want it to compare June of each year. How do I do that?" You, again, have to rewrite the query, or create a new report on top of a modified query. Excel? You click the little arrow next to the column list, and select the ones you want to display.
And if the data were inserted as two columns instead of month and year in one column, Excel would require only that you select the specific month you want once, to filter a year-to-year comparison.
I just don't like rewriting code every time some data changes or there's a slightly different need. Use a real interface that lets the user decide what they want and how they want it. More scalable.
(By the way, thank you for the compliment.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 12:47 pm
Well, yes. Pivot tables are fiddly to do 'on the fly' in SQL Server I grant you. Also, you get free graphs, and drill-down if you use Excel. I quite often deliver reports as Excel pivot-tables myself where this seems the more appropriate way to do them. However, it is certainly possible to do nice Pivot tables in SQL Server, and as you've shown, they're quicker. I often have to deliver a lot of reports as Emails or update a lot of web-based daily reports, and, for this, SQL Server is ideal.
I use routines similar to the stored procedure that Robyn Page and I did for Simple-Talk [/url]
/*
e.g.
Execute spDynamicCrossTab
@RowValue='firstname+'' ''+lastname',
@ColValue='Year(OrderDate)',
@Aggregate= 'count(*)',
@FromExpression='FROM Employees INNER JOIN Orders
ON (Employees.EmployeeID=Orders.EmployeeID)',
@ColOrderValue='Year(OrderDate)',
@Title ='No. Sales per year',
@SortBy ='total desc' --what you sort the rows by (column heading)
Execute spDynamicCrossTab
@RowValue='firstname+'' ''+lastname',
@ColValue='DATENAME(month,orderDate)',
@Aggregate= 'sum(subtotal)',
@FromExpression='FROM Orders
INNER JOIN "Order Subtotals"
ON Orders.OrderID = "Order Subtotals".OrderID
inner join employees on employees.EmployeeID =orders.EmployeeID',
@ColOrderValue='datepart(month,orderDate)',
@Title ='Customers orders per month '
EXECUTE spDynamicCrossTab
@RowValue='country',
@ColValue='datename(quarter,orderdate)
+case datepart(quarter,orderdate)
when 1 then ''st''
when 2 then ''nd''
when 3 then ''rd''
when 4 then ''th'' end',
@Aggregate= 'sum(subtotal)',
@FromExpression='FROM Orders
INNER JOIN "Order Subtotals"
ON Orders.OrderID = "Order Subtotals".OrderID
inner join customers on customers.customerID =orders.customerID',
@ColOrderValue='datepart(quarter,orderDate)',
@sortby='total desc',
@Title ='value of orders per quarter'
*/
ASSET NOCOUNT ON
DECLARE
@Command NVARCHAR(MAX)DECLARE @SQL VARCHAR(MAX)--make sure we have sensible defaults for ordersSELECT @ColOrderValue=COALESCE(@ColOrderValue, @ColValue),@Sortby=COALESCE(@SortBy,@RowValue),
@rowsort=COALESCE(@RowSort,@RowValue)--first construct tha SQL which is used to calculate the columns in a
--string
SELECT @Command='select @sql=coalesce(@SQL,''SELECT['
+@Title+']=case when row is null then ''''Sum''''else convert(Varchar(80),[row]) end ,
'')+
''[''+convert(varchar(100),'
+@ColValue+')+''] =sum( CASE col WHEN ''''''+convert(varchar(100),'
+@ColValue+')+'''''' THEN data else 0 END ),
'' '
+@FromExpression+'GROUP BY '
+@ColValue+'order by max('
+@ColorderValue+')'--Now we execute the string to obtain the SQL that we will use for the--crosstab query
EXECUTE sp_ExecuteSQL @command,N'@SQL VARCHAR(MAX) OUTPUT',@SQL OUTPUTIF @@error > 0 --display the string if there is an error
BEGIN
RAISERROR
( 'offending code was ...%s', 0, 1, @command )RETURN 1
END
IF
@debugging <>0 SELECT @Command--we now add the rest of the SQL into the stringSELECT @SQL=@SQL+' [Total]= sum( data )from
(select [row]='
+@RowValue+',[col]='
+@ColValue+',[data]='
+@Aggregate+',[sort]=max('
+@rowsort+')'
+@FromExpression+'GROUP BY '
+@RowValue+', '+@ColValue+')f
group by row with rollup
order by grouping(row),'
+@Sortby--and execute itIF @ReturnTheDDL<>0 SELECT @SQL ELSE EXECUTE (@SQL)IF @@error > 0
BEGIN
RAISERROR
( 'offending code was ...%s', 0, 1, @sql )RETURN 1
END[/font]
@colValue='month',
@rowValue='name',
@Aggregate= 'sum(amount)',
@Rowsort='name',
@FromExpression='FROM sales',
@ColOrderValue='month',
@Title ='Customers orders per month ',
@sortby='row'[/font]
@colValue='month',
@rowValue='name',
@Aggregate= 'sum(amount)',
@Rowsort='name',
@FromExpression='FROM sales where month in (''FEB-08'',''MAR-08'')',
@ColOrderValue='month',
@Title ='Customers orders per month ',
@sortby='row' [/font]
Best wishes,
Phil Factor
July 28, 2008 at 2:00 pm
Every tool has it's right place. For what you're talking about, I'd use dynamic SQL too (and probably crib it from your workbench - I use a lot of the stuff you two have posted). My statement about using Excel is a general suggestion, not an absolute.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2015 at 8:16 am
Hi,
I too need to transpose row data column wise but the Input Which I Have is
name Jun-08 Feb-08 Mar-08 total
-------------------- ----------- ----------- ----------- -----------
AAA 0 0 200 200
ABA 200 0 0 200
ABC 100 200 0 300
ACC 0 200 0 200
XXX 0 0 100 100
XYZ 0 150 100 250
& I need the output other Way i.e.
Name Month Amount
------ ------ -------
ABC Jun-08 100
XYZ Feb-08 150
AAA Mar-08 200
ABC Feb-08 200
XYZ Mar-08 100
Please Answer me how do i do this
June 18, 2015 at 9:43 am
[p]I hope this helps[/p]
[font="Courier New"]
-- we prepare the sample data and put it in a temp table
SELECT * INTO #sampleData
FROM
(SELECT 'AAA' AS name, 0 AS 'Jun-08' , 0 AS 'Feb-08', 200 AS 'Mar-08', 200 AS 'Total'
UNION ALL SELECT 'ABA', 200, 0, 0, 200
UNION ALL SELECT 'ABC', 100, 200, 0, 300
UNION ALL SELECT 'ACC', 0, 200, 0, 200
UNION ALL SELECT 'XXX', 0, 0, 100 ,100
UNION ALL SELECT 'XYZ', 0, 150, 100, 250)f
-- and now we simply do the rotation.
SELECT name, CONVERT(DATETIME,'1 Jun 2008',113) AS 'Month', [Jun-08] AS amount FROM #sampleData WHERE [Jun-08]>0
UNION ALL SELECT name, CONVERT(DATETIME,'Feb 2008',113), [Feb-08] FROM #sampleData WHERE [Feb-08]>0
UNION ALL SELECT name, CONVERT(DATETIME,'Mar 2008',113), [Mar-08] FROM #sampleData WHERE [Mar-08]>0
[/font]
Best wishes,
Phil Factor
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply