Move Data From One Column To Another Column And Row
Aug 7, 2006
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
Basic Information needed for Life Insurance 25 45
Ea Chairmans Conference 10 10
EA Chairmans Conference Press Release 33 21
EA Chairmans Inner Circle Press Release 16 46
EA Honor Ring Press Release 13 35
EA National Conference Press Release 6 9
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.
Below is the code I am using now, but since the moving of the data is done in code it is very slow.
****************** 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.
Okay, after I got everything imported, I found that a few thousand columns had "Shifted" on me. So now I am trying to "Shift them over" to where they need to be.
I did this: INSERT INTO dbo.TABLENAME (COLUMN_NAME_TO_BE POPULATED) SELECT COLUM_NAME_OF_INFO_TO_BE_MOVED FROM dbo.TABLENAME WHERE MFG = 'MANUFACTURER_NAME' AND PN LIKE '8888888888' GO
I populated the PN column with 8888888888 to use as a reference point, and the MFG column already was populated with the correct name, so I was using those as my unique reference points.
Other columns that have the same MFG name are correct, so I have to use two unique identifiers to specify the actual data that needs to be moved.
It inserted 'NULL' in the whole table after I ran it in the Query Analyzer. It appeared to disregard the WHERE statement all together Any ideas on what I am doing wrong here?
Is there a way to move the data from one column to the next one over by specifying other WHERE criterias?
I am looking for a solution to move all Part Numbers for a specific manufacturer that is 9 digits long to a new column.
Let me explain............
I have a product table that has three columns of product codes associated with that product (Part Number as PN, Series as Series, and Industry Code as ICDE). I now want to create a fourth column (Manufacturer Code as MCDE) as my database has grown for another product specific numerical designator that is specific for one manufacturer.
Currently I have the specific 9 digit codes in the same column as the Part Numbers, though they all have their specific rows (Part Numbers & Manufacturer Codes are not in the same cell).
Now the Part Numbers have various numbers, letters, and special characters, but the specific 9 digit manufacturer codes are pure numbers.
So my question is............
How does one go about moving these specific 9 digit codes to their new column and out of the Part Number column?
They will all be exactly 9 digits, no special characters, no letters, no spaces.
Is there a way to tell MS SQL to just move anything for that specific manufacturer that has the 9 digit manufacturer numbers to the new column, bypassing anything with letters, special characters or any part number that is not a pure 9 digit number?
I have two tables. They both have an identity field to join them together. One of the tables has a column of data that I want to put in the other table. I need to make sure the data is brought over using the join so it updates that column for the right record.
I am struggling with this. Should I use a update or an insert? I'm leaning toward update but I can't figure out how with the join.
This is what I have tried so far:
Update grpcon.GroupID = groups.GroupID from grpcon Inner Join groups on grpcon.GroupNum=groups.GroupNum
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[TrialBalance]( [Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
I have a table A I have Five columns and Each consist of Five rows of textcolumn 1column 2column 3column 4column 5Thru Sql coding i want to move from one column to another column - When Date Changes I need ASp and sql code for this Or ATleast i need sql query for how to move one column to another
I created SQL table follow by XSD fileAnd when any users added new column to XSD in ordinal position = 3 But after my program successfully created new column, its position is the last position What can I do ???? I suspect why I can't set it (I've looked for solution on MSDN already)even though We can see ordinal position bythis query SELECT *FROM INFORMATION_SCHEMA.Columns What can I do for solving ???? Help me please
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
Using MDS 2012: I have an entity "XYZ_Entity". In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".
While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.
Hi, I was wondering how I can complete a column (which doesnt have an input one) with data. For example:
I have a sql query which bring data of 3 columns
ID | FISRT NAME | LAST NAME 1 MIKE MORGAN 2 SARA JOHANES
So, I will insert that data in a FLAT FILE CONNECTION MANAGER, which I configured with 3 columns and I did the corresponding mapping in the FLAT FILE DESTINTATION.
Now, If I add one more column in the FLAT FILE CONNECTION MANAGER, I will not have it mapped to a input one, obviously. So, what I need is to add one more column to the flat file destination and complete it with zeros values in it.
Probably I can solve this part by introducing a DERIVED COLUMN and there I can configure the zeros that I want to add to the column. But I'm not sure if I can do that without having a input column. So, the question will be, how can I add one column to a flat file which doesnt have a input and introduce any value that I want to it? Hope I was clear Thanks for your help.
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
Hi everyone, I am using SSIS, and I got the folowing error, I am loading several CSV files in a OLE DB, Becasuse the file is finishing and the tak dont realize of the anormal termination, making an overflow. So basically what i want is to control the anormal ending of the csv file. please can anyone help me ???
I am getting the following error after replacing the '""' with '|'. The replacng is done becasue some text sting contains "" wherein the DFT was throwing an error as " The column delimiter could not foun".
[Flat File Source [8885]] Error: The column data for column "CountryId" overflowed the disk I/O buffer. [Flat File Source [8885]] Error: An error occurred while skipping data rows. [DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (8885) returned error code 0xC0202091. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.
[DTS.Pipeline] Information: Post Execute phase is beginning.
Hi there, I am a new member of this site and I am not very much aware of T-sql's working. My question is what if I need to get one column's data to be the heading of another column. To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this StuID - - - SubID - - - -Tot - - -Obt 1 - - - - - - -1 - - - - - - -50 - - - 38 1 - - - - - - -2 - - - - - - -50 - - - 41 1 - - - - - - -3 - - - - - - -50 - - - 42 1 - - - - - - -4 - - - - - - -50 - - - 40 2 - - - - - - -1 - - - - - - -50 - - - 35 2 - - - - - - -2 - - - - - - -50 - - - 40 2 - - - - - - -3 - - - - - - -50 - - - 42 2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4 1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40 2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted. Thanks.
I have a column that contains extra info that needs to be moved to another column or deleted alltogether. is there a way to select these items and move them to another column, leaving the rest of the data in the original column?
EXAMPLE
MYTABLE >COLUMN1 May have Data Like: ABCDE123 SER1 or XYZ12DEFSer1:1
WHAT I NEED TO DO IS Move anything after the SER1 to a new column and retain the rest of the data in the original column. making it look something like this:
COL1 COL2 ABCDE123 SER1 XYZ12DEF Ser1:1
Another question is if there is a way to delete extra spaces? Like make all data that has two or more extra spaces, just single spaces and any additional spacing after a row of data, delete all additional spaces after the last letter/character/number.
One more question - What would be a great resource to learn MS SQL in more depth?
I am trying to learn ASP/VBScript/JavaScript/ and now MS SQL all at once b/c this is what my business depends on. Trying to be 5,000 hats at once can get confusing and overwhelming, so I am looking for any "Crash course" I can to learn as much, as fast as possible. Any direction or ideas?
If I run an UPDATE query on a table which is partitioned by the column I am updating - will the records be moved to another partition?
ie. I have a table where Historical bit column marks whether a particular record should go to Partition1 (=0) or Partition2 (=1). Now, I update a record in that table and change the Historical column value from 0 to 1. What happens with that record?
in Table A Column is PriKey No Name 1 1 Apple 2 2 Orange 3 3 Juicy in Table B column is Prikey ColumnA Price 1 1 10 2 3 2 3 2 5
TableA.Prikey have Depency with TableB.ColumnA
when I am trying to Delete data from Table A , I got error message becaue the depency how to delete data when there have depency?
I just know when table have trigger , we can disable trigger before delete Data, and enable trigger when data deleted does there have a way to disable depency and then enable ?
This one has bothered me ever since sql server 2000.
When you do an insert into a table with literally hundreds of char or varchar columns and you get the error that the insert failed due to data loss/truncation on a column.
Is there anyway in 2005 to actually find out what column ? Since there are hundreds is is literally a long process of going though each column 1 by 1 manually.
The database engine surely MUST know what column this occurred on so why can it not tell you which column the truncation occurred on ?
Can this be done in 2005 if not will this information be available in 2008 ?
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A B C D E F G H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
I have a problem i'v been searching all day but i can't find an answer anywhere maybe someone here can help. What I want to do is give a column in a table the same value as another column from the same table. For example: Table:Requests A request has a relatedrequestId wich links another request to it. Now I want the date from the linked request in the date from the master request. Because all the master requests date's are empty and i want them to have the date from the linked request.
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is : Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be created where the referencing column 'child_table.child_id' is an identity column.
Hi guys, If I have a temporary table called #CTE With the columns [Account] [Name] [RowID Table Level] [RowID Data Level] and I need to change the column type for the columns: [RowID Table Level] [RowID Data Level] to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time. What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance, Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE ALTER COLUMN [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE MODIFY [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'. ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
Hi, I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.