Move data from one column to another column and row

I need some help converting a table which has one row for each year.  Displaying the data so the each row will contain all of the information for the title and all of the years displayed in separate columns.

I have a SQL Querry which returns the data looking like this (the querry is grouping by Title and by year, then adding up the quantity of rows):

Basic Information needed for Life Insurance 25 2005
Basic Information needed for Life Insurance 45 2006
Ea Chairmans Conference 10 2005
EA Chairmans Conference Press Release 33 2005
EA Chairmans Conference Press Release 21 2006
EA Chairmans Inner Circle Press Release 16 2005
EA Chairmans Inner Circle Press Release 46 2006
EA Honor Ring Press Release 13 2005
EA Honor Ring Press Release 35 2006
EA National Conference Press Release 6 2005
EA National Conference Press Release 9 2006

I need this data displayed like this:

Title 2005 2006 Total
Basic Information needed for Life Insurance 25 45 70
Ea Chairmans Conference 10   10
EA Chairmans Conference Press Release 33 21 54
EA Chairmans Inner Circle Press Release 16 46 62
EA Honor Ring Press Release 13 35 48
EA National Conference Press Release 6 9 15

What I want to do is to basically say:

If Row 1 Title = Row 2 Title and Row 1 Year = Row 2 (Year + 1)) Then

   add to grid Row 1 Title, Row 1 Qty, Row 2 Qty

   Skip Row 2 and go to Row 3  (repeat this step until end of data)

End

Is there any way in the SQL querry to retrieve this data in this format so that I can display it in a datagrid? 

Is there any way to display the data in this pattern.

 

0
dsmayer
8/4/2006 5:27:20 PM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

8 Replies
1065 Views

Similar Articles

[PageSpeed] 0

This would be best handled at the database level instead of the presentation layer. Please post your SQL code. 
0
ayachin
8/4/2006 7:28:39 PM

Thank you very much for your help.

This is what I have in code:

'****************** Global Variables

Dim dbName As String = "Region_Web_Page_Hits"
Dim ColumnCount As Integer

 


Function ReturnColumnHeaderAndHitsDataSet() As System.Data.DataSet
'******** This will bring back the column Headers and Gross Hits per Column
 Dim ColumnHeaderAndHitsQueryString As String = "SELECT DISTINCT TOP 100 PERCENT " & _
  "YEAR(dat_Date) AS str_Year, COUNT(str_PageName) AS int_YearHits " & _
  "FROM dbo.tbl_web_page_hits " & _
  "WHERE (str_URL LIKE N'%press_Release%') " & _
  "GROUP BY YEAR(dat_Date) " & _
  "HAVING (Not (Year(dat_Date) Is NULL)) " & _
  "ORDER BY YEAR(dat_Date)"

 Dim ColumnHeaderAndHitsDataSet = LoadDataArray(ColumnHeaderAndHitsQueryString, dbName)
 Return ColumnHeaderAndHitsDataSet
End Function


Function ReturnTableDataSet() As System.Data.DataSet
'This query string will return the data needed to display the report.
 Dim TableQueryString As String = "SELECT TOP 100 PERCENT " & _
  "str_PageName, COUNT (str_PageName) AS int_Hits, YEAR(dat_Date) AS str_Year " & _
  "FROM dbo.tbl_web_page_hits WHERE str_URL LIKE '%Press_Release%' " & _
  "GROUP BY str_PageName, YEAR(dat_Date) ORDER BY str_PageName"
 Dim TableDataSet = LoadDataArray(TableQueryString, dbName)
 ColumnCount = TableDataSet.Tables(0).Columns.Count
 Return TableDataSet
End Function

'******* Function to Load the Data Array
Function LoadDataArray(ByVal queryString, ByVal dbName) As System.Data.DataSet
 Dim ConnectionString As String = ConnectionStr(dbName)
 Dim sqlConnection As SqlConnection = New SqlConnection(ConnectionString)
 Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
 Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand)
 Dim dataSet As DataSet = New DataSet
 dataAdapter.Fill(dataSet)
 sqlConnection.Close()
 Return dataSet
End Function

 

Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

   If Not Page.IsPostBack Then
        Dim Sort_Field As String = ""
        dg_Data.DataSource = QueryMethod(Sort_Field)
        dg_Data.DataBind()
   End If
End Sub

Currently I have a bunch of Classic ASP code which puts the data in to an array and then into a table with a series of if statments, but it is really slow. 

I want to bind it to a data grid and if possible let the SQL statement format the data for me.

 

Thanks again for your help.

0
dsmayer
8/4/2006 7:46:55 PM
No wonder it's slow to load.  Your code is bloated with unnecessary functions.  First you need to convert your SQL strings into stored procedures.  Using stored procedures will vastly improve your performance.  Secondly the use of stored procedures will allow you to take your data and manipulate it into the format you want.  Thirdly you will be able to build your datagrid with 1 single function.

I've pieced together a stored procedure that should half way accomplish what you need. Run this through query analyzer and see what happens.

Create Proc [dbo].[MyProc]
(
    @Press_Release VarChar(25)
)
AS

--Destroy any existing temp tables
if exists (select * from [TempDB].[dbo].sysobjects where name = '##TempTable')
    Begin
        Drop table ##TempTable
    End


SELECT str_PageName as Title, COUNT (str_PageName) AS int_Hits, YEAR(dat_Date) AS str_Year, Total
    into ##TempTable
    FROM dbo.tbl_web_page_hits with(noLock)
        WHERE str_URL LIKE in (@Press_Release)
        GROUP BY str_PageName, YEAR(dat_Date) ORDER BY str_PageName


--Pivot the data into a result table.
Select Title,
        sum(case when str_Year = '2005' then int_Hits end) as '2005',
        sum(case when str_Year = '2006' then int_Hits end) as '2006',
from ##TempTable with(noLock)
group by Title
0
ayachin
8/7/2006 7:11:05 PM

Thank you again for your help in this.  I am new to .net and stored procedures.  I realize that querying the data through a stored procedure and I have been planning on moving these functions into stored procedures after I perfected them.

This is the storted procedure which you suggested I created (I simply cut and paste from your code):

Create Proc [dbo].[MyProc]
(
    @Press_Release VarChar(25)
)
AS

--Destroy any existing temp tables
if exists (select * from [TempDB].[dbo].sysobjects where name = '##TempTable')
    Begin
        Drop table ##TempTable
    End


SELECT str_PageName as Title, COUNT (str_PageName) AS int_Hits, YEAR(dat_Date) AS str_Year, Total
    into ##TempTable
    FROM dbo.tbl_web_page_hits with(noLock)
        WHERE str_URL LIKE in (@Press_Release)
        GROUP BY str_PageName, YEAR(dat_Date) ORDER BY str_PageName


--Pivot the data into a result table.
Select Title,
        sum(case when str_Year = '2005' then int_Hits end) as '2005',
        sum(case when str_Year = '2006' then int_Hits end) as '2006',
from ##TempTable with(noLock)
group by Title

The Lines in red are giving me the following error messages:

Server: Msg 156, Level 15, State 1, Procedure MyProc, Line 17
Incorrect syntax near the keyword 'in'.
Server: Msg 156, Level 15, State 1, Procedure MyProc, Line 25
Incorrect syntax near the keyword 'from'.

Do you have any suggestions as to why the errors?

Thanks again.

0
dsmayer
8/7/2006 8:54:14 PM

Hi,dsmayer

I don't think that 'like' and 'in'can be used together. May be it shoud be WHERE str_URL LIKE  '%'+@Press_Release+'%'




Best Regards,
__________________________________________________
Sincerely,
Rex Lin
Microsoft Online Community Support

This posting is provided "AS IS" with on warranties, and confers no rights.
0
rexlin
8/8/2006 2:03:58 AM

Yes and to follow up the second error you need to remove the comma from the second case line. 

 As mentioned I pieced the stored procedure together but could not test it therefore errors will occur.

0
ayachin
8/8/2006 1:12:16 PM

Thanks for all of your help.  When I break down the steps into "views" for testing your statements everything works.

Putting it into a stored procedure using the "fixed" paramater, the procedure returns the correct information. (The line in blue)

CREATE PROCEDURE sp_WebHitsByCategory
       @str_title nvarchar(50)

AS
--Destroy any existing temp tables
if exists (select * from [TempDB].[dbo].sysobjects where name = '##TempTable')
    Begin
        Drop table ##TempTable
    End


SELECT str_PageName as str_PageName,  COUNT (str_PageName) AS int_Hits, YEAR(dat_Date) AS str_Year
    into ##TempTable
    FROM dbo.tbl_web_page_hits with(noLock)
        WHERE str_URL LIKE ('%press_release%')
        GROUP BY str_PageName, YEAR(dat_Date) ORDER BY str_PageName


--Pivot the data into a result table.
Select str_PageName,
        sum(case when str_Year = '2005' then int_Hits end) as '2005',
        sum(case when str_Year = '2006' then int_Hits end) as '2006'
from ##TempTable with(noLock)
group by str_PageName
GO

When I change the "fixed paramater" to @str_title I do not return any values.

CREATE PROCEDURE sp_WebHitsByCategory
       @str_title nvarchar(50)

AS
--Destroy any existing temp tables
if exists (select * from [TempDB].[dbo].sysobjects where name = '##TempTable')
    Begin
        Drop table ##TempTable
    End


SELECT str_PageName as str_PageName,  COUNT (str_PageName) AS int_Hits, YEAR(dat_Date) AS str_Year
    into ##TempTable
    FROM dbo.tbl_web_page_hits with(noLock)
        WHERE str_URL LIKE ('%@str_title%')
        GROUP BY str_PageName, YEAR(dat_Date) ORDER BY str_PageName


--Pivot the data into a result table.
Select str_PageName,
        sum(case when str_Year = '2005' then int_Hits end) as '2005',
        sum(case when str_Year = '2006' then int_Hits end) as '2006'
from ##TempTable with(noLock)
group by str_PageName
GO

The passed value is coming from a drop down list.  I used the Visual Studio debug feature and discovered that the variable is being passed into the stored procedure.   Here is my code:

Public

Function getDataReader()

            'Dim strSQL As String

            Dim dbComm As SqlCommand

            Dim dbReader As SqlDataReader

            Dim oSqlConn As SqlConnection

            Dim dbName As String = "Region_Web_Page_Hits"

         oSqlConn =

New SqlConnection(ConnectionStr(dbName))

         oSqlConn.Open()

         dbComm =

New SqlCommand("dbo.sp_WebHitsByCategory", oSqlConn)

         dbComm.CommandType = CommandType.StoredProcedure

         dbComm.Parameters.Add(

"@str_Title", Trim(ddl_Category.SelectedItem.Value))

      ' dbComm.Parameters.Add("@str_Date", ddl_Date.SelectedItem.Value)

         'Response.Write(@str_Title)

      dbReader = dbComm.ExecuteReader()

'Debug Code

Debug.WriteLine(Trim(ddl_Category.SelectedItem.Value))

'Debug.WriteLine(dbReader("Title")) ' This returns an error message "Invalid attempt to read when no data is present"

Return dbReader

End Function

 

Can you possibly see why no information is being returned from the stored procedure.

0
dsmayer
8/8/2006 9:04:41 PM
You are not opening the dbreader to read the data.

 While dbReader.Read() 
Debug.WriteLine(dbReader("Title"));
End While

0
ayachin
8/9/2006 5:24:26 PM
Reply:

Web resources about - Move data from one column to another column and row - asp.net.presentation-controls

Chicago police will add more body cameras for officers
Mayor Rahm Emanuel and Chicago Police Superintendent Garry McCarthy said additional cameras will enhance public trust, although their statement ...

Obama makes surprise visit to Le Bataclan to honor victims of Paris terrorist attacks
Having just arrived in Paris for the the upcoming COP21 climate summit, President Barack Obama's motorcade from Orly Airport made a surprise ...

The Logic Pros: Black Friday/Cyber Monday deals – MIDI keyboards, Lightning/USB mics, iOS synths, plug-ins ...
In this week’s episode of The Logic Pros , we will be taking a short break from the usual routine to stock up on discounted iPad synth apps, ...

Deadspin Up All Night: Bitch You Better Off Voting For Donald Trump
Thank you for your continued support of Deadspin. As you were. Read more...

Turkey to hand over body of dead Russian pilot to Moscow: PM
The body of a Russian pilot killed when his plane was shot down by Turkey last week will be handed over to a Russian representative after being ...

Hunt on for mother of baby buried alive
New York Post Hunt on for mother of baby buried alive Toledo Blade COMPTON, Calif. — Authorities are looking for the mother of a newborn baby ...

New Photos Of Princess Charlotte Released By William And Kate As Thank You To Media For Allowing The ...
New photos of Princess Charlotte, the fourth in line to the British throne, have been released by Kensington Palace as a “thank you” to the media ...

Turkey To Help Stem Flow of Refugee Migrants to Europe
With the ongoing flow of refugee migrants moving from Syria through Turkey and into European Union countries, the EU has asked Turkey to help ...

A Message For Our Republican Amigos Christie? Trumpf? There's A Better Solution To Your Anguish
Artist Darkblack foresees a Jersey Bounce in New Hampshire The funnest news Saturday night was the announcement that New Hampshire's Union Leader ...

Pope Francis is welcomed to the Central African Republic amid recent violence
Pope Francis has arrived in the conflict-torn Central African Republic for the third and final leg of his trip to Africa, brushing aside security ...

Resources last updated: 11/30/2015 1:18:11 AM