1

I have to design a report laid out in a way which differs to how the data is currently stored in the database.

There is a column in Table A, which contains the names of a number of charges, in this report I have to take each charge in this one column and place them in their own respective columns with other data. My idea was to create a temporary table (Table B) and populate the table and return it in a stored procedure like below (forgive my terrible paint skills)

enter image description here

I had a look on stack Overflow and tried my hand at this solution I found:

Procedure to create a table with a variable number of columns

My Code is as follows below (I haven't included the keywords like Begin/End and such)

I use the first table #tempChargeTypeTable to take note of all the charge types, which works fine.

--Declare temporary table to hold different charges in.
CREATE TABLE #tempChargeTypeTable  
(
    ChargeName NVARCHAR(50)
)

--Declare variable to hold value name through each loop
DECLARE @ColumnName NVARCHAR(50)

--Cursor for running through the the chargeTypeTable
DECLARE ChargeCursor CURSOR FOR SELECT ChargeName FROM #tempChargeTypeTable

--Declare variables for dynamic sql query
DECLARE @sqlQuery NVARCHAR(4000)

--Populate the temporary charge type table
INSERT INTO #tempChargeTypeTable 
SELECT DISTINCT ChargeType.Name
FROM dbo.ChargeType

This below is to create the temporary table to hold all the data I need for the report, However it doesn't seem to be creating the table at all despite the code building fine with no errors

--Create a new table to hold all the information via a dynamic sql query

SET @sqlQuery = 'CREATE TABLE ##tempPaymentsAnalysisTable(MemberID NVARCHAR(50) ,FirstName NVARCHAR(50) ,
                                                           Surname NVARCHAR(50) , CategoryName NVARCHAR(50) ,'


OPEN ChargeCursor

FETCH NEXT FROM #tempChargeTypeTable INTO @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sqlQuery = @sqlQuery + RTRIM(@ColumnName) + ' NVARCHAR(50), '

FETCH NEXT FROM ChargeCursor INTO @ColumnName

END

CLOSE ChargeCursor

DEALLOCATE ChargeCursor

SET @sqlQuery = @sqlQuery + ')'

EXEC  @sqlQuery


--SELECT Statement for Test
SET @sqlQuery = 'SELECT * FROM ##tempPaymentsAnalysisTable'
Exec @sqlQuery

I've been at this for a while and I reckon I've maybe my query set wrong. Can anyone spot anything the matter?

Update: I'm using SQL Server 2012 for the database

Many thanks

Community
  • 1
  • 1
IrishCrf
  • 105
  • 3
  • 13
  • Which dbms? (Doesn't look like ANSI SQL...) – jarlh Feb 25 '15 at 10:28
  • Sorry forgot to mention, I'm using SQL Server 2012 and Management Studio. I'll update my question – IrishCrf Feb 25 '15 at 10:32
  • have you looked into pivot it can transform rows into columns. Post some sample data and sample report of how you want the report to look. – akhil vangala Feb 25 '15 at 10:34
  • I haven't to be honest, I'll look up some documentation about it now and get some sample data back to you. – IrishCrf Feb 25 '15 at 10:37
  • 1
    You are not dropping the ##temp table which will remain in scope until you drop it. see http://stackoverflow.com/questions/7887011/how-to-drop-table-if-exists-in-sql-server-2005 for how. – Jeremy Feb 25 '15 at 10:47
  • 1. You will have syntax error. You are adding `RTRIM(@ColumnName) + ' NVARCHAR(50), '`. You should remove the last comma after cursor. 2. Use table variable @t rather then temp table `##t` 3. You will not be able to select from `##t` anywhere but @sqlQuery 4. Avoid such style – Giorgi Nakeuri Feb 25 '15 at 10:56

1 Answers1

0

With sample data here is what you can try with your query

enter image description here

Hopefully this will help you

CREATE TABLE #CourseSales
(Course VARCHAR(50),Year INT,Earning MONEY)
GO
--Populate Sample records
INSERT INTO #CourseSales VALUES('.NET',2012,10000)
INSERT INTO #CourseSales VALUES('Java',2012,20000)
INSERT INTO #CourseSales VALUES('.NET',2012,5000)
INSERT INTO #CourseSales VALUES('.NET',2013,48000)
INSERT INTO #CourseSales VALUES('Java',2013,30000)
GO

SELECT * FROM #CourseSales



DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM #CourseSales
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
akhil vangala
  • 1,043
  • 1
  • 10
  • 11