I have data like this in a table
ID test_date Score
001 4/1/2014 80
001 5/4/2014 85
001 6/1/2014 82
I want to convert the data into a row like this:
ID test_date1 score1 test_date2 score2 test_date3 Score3
001 4/1/2014 80 5/4/2014 85 6/1/2014 82
How can I do that with T-SQL?
I have a table with column "Data" as VARCHAR, with entries like below.
1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68
I want to select the data by converting varchar to int for numeric values and for alphanumeric it should display as it is.
SELECT CAST(dataAS INT) FROM record_tab
getting below error
Conversion failed when converting the varchar value 'A1'
I have data in the below format .
NameValuecategory
AAA510
BBB510
CCC510
DDD512
EEE512
FFF512
I want the result in the below format
NAMEValuecategory
AAA,BBB,CCC510
DDD,EEE,FFF5120
I have tried stuff but all six values(AAA...FFF) are coming in one row , however i need them as per the category.
I put on the site excell file where I explained what I want to get . It is easier to explain through an example .
THIS IS MY INPUT DATA:
ACCOUNTS CODE ACCOUNTS NAME TOTAL
0 CLASS 0
011 Synthetic group
011-1301 Some name 1 25 $
011-1401 Some name 2 20 $
022 Synthetic group
022-1011 Some name 1 101 $
[code]...
I have system id information in table system_ids and productids and systemidinsformation has lot of data but I am looking two strings in tire data to pull into two separate columns. details below
Database versions :ms sql 2008/2012
tablename:system_id's
column:system id information
sample data from system_id_information column
########################################
<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="ArrayOfHostSystemIdentificationInfo"><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue> unknown</identifierValue><identifierType><label>Asset Tag</label><summary>Asset tag of the system</summary><key>AssetTag</key></identifierType>
[Code] .....
I am looking output of two columns, which are bolded
product_id snumber
654081-B21 MXQ43905SW
for serial number this is common
before string :HostSystemIdentificationInfo"><identifierValue>
and after string </identifierValue><identifierType><label>Service tag
and snumber is always between the before and after string and number of characters of snumber varies and entire data for a row also varies
I want to display data in block. Below is the Create,Insert Script and format of desired output.
CREATE TABLE [dbo].[Test_AA](
[CounterpartID] [varchar](2000) NULL,
[CounterpartName] [varchar](2000) NULL,
[SATName] [varchar](2000) NULL,
[NameOfBO] [varchar](2000) NULL,
[Code] ....
Result Should be like This :
ID:17388
Name:Scottie Berwick
SATName:Scottie Berwick
NameOfBO:
SATAccountNumber:
[Code] ....
I have a database table that was designed by someone else, and I'm trying to see if I can normalize the pattern. Here's the dummy table:
CREATE TABLE [dbo].[BadTox](
[PatientID] [int] NULL,
[Cycle] [tinyint] NULL,
[ALOPECIA] [tinyint] NULL,
[Causality1] [tinyint] NULL,
[Code] ....
All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:
CREATE TABLE Symptom (
PatientID INT NOT NULL,
Cycle TINYINT NOT NULL,
SymptomName VARCHAR(20) NOT NULL, -- from the source column *name*
Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase
PRIMARY KEY (PatientID, Cycle, SymptomName));
I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...
I can retrieve all the column names that are in uppercase using this:
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'SCRIDB');
SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox');
SELECT name AS column_name
, column_id AS col_order
FROM sys.all_columns
WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS
AND object_id = @object_id;
but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):
ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply]
AS
SELECT PatientID
, Toxicity
, MAX(Grade) AS MaxGrade
[code]....
The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.
So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END
However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
We are storing changed data of tables into XML format for auditing purpose. The functionality is already achieved. We are using FOR XML Path clause to convert relational data of tables into XML format.
Now, a table is having column name with '(' . For example name of the column is, ColumnName(). In this case we can not convert into XML using For XML clause. Showing error as,
Column name 'columnName()' contains an invalid XML identifier as required by FOR XML; '(' (0x0028) is the first character at fault.
Need to change the datatype of existing column which has huge data.
I'm performing below steps
1. Create new column with correct datatype in the same table
2. copy data into new column
3. drop indexes on column
4. <<<>>>
now the existing column also has many SP dependent and I do not wish to drop them.
5. rename existing column to xxx
6. rename new column to correct column
7. drop old column
8. make required indexes
Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
For example:
PostCodeLatitudeLongitude
66000/6610042.6965952.899370
20251/2027042.1964719.404951
Would become
PostCodeLatitudeLongitude
6600042.6965952.899370
6610042.6965952.899370
2025142.1964719.404951
2027042.1964719.404951
how this can be done?
Hi members
I got the result set as shown below (By executing another query i got this).
Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7
But, I need the same result set as below
Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7
Can anyone provide me the tips/solution.
I want to convert all the column of table to ToBase64String.
Public Class ScriptMain
Inherits UserComponent
Dim md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider()
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim columnContents As Byte() = UnicodeEncoding.Unicode.GetBytes(Row.Col001 + Row.Col002 + Row.Col003 + Row.Col004 + Row.Col005 + Row.Col006 + Row.Col007 + Row.Col008 + Row.Col009 + Row.Col010 + Row.Col011 + Row.Col012 + Row.Col013 + Row.Col014)
Dim hash As Byte() = md5.ComputeHash(columnContents)
Dim hashString As String = Convert.ToBase64String(hash, Base64FormattingOptions.None)
Row.RowChecksum = hashString
End Sub
End Class
I have a string stored in a column of type image that I need to do string operations with in order to decode the containing infomation.
data [image] = 0x07FD0707FD0102F001000054004C005300410000000054004C00530041000000FF...
I can't cast or convert to nvarchar(max), varchar(max) as I always get an error. Explicit conversion from data type image to varchar(max) is not allowed.
I tried CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), DATA))
Which doesn't throw an error but seems to interprete the hexadecimal code to ascii characters and therefore is useless (ýýð...)
I need to take multiple rows in a table that store fiscal data and denormalize into one row with all the fiscal information in it. any suggestions on how to approach this creatively - lots of data will make it important to make this efficent, will need to run each year so I would like to make it portable.
Fiscal data table
companycode 1 1 1
fiscalyear 2008 2008 2008
fiscalperiod 200801 200803 200802
amount 101 301 201
result set
companycode 1
fiscalyear 2008
fiscalmonth1_amount 101
fiscalmonth2_amount 201
fiscalmonth3_amount 301
fiscalmonth4_amount
..
fiscalmonth12_amount
thanks
I need to convert a column of data from alpha numeric to integer.
I am only querying the tables i.e. i don't have access to actually change the data tables themselves.
CAST or CONVERT throws up an error. Are there any other commands i can use at the query stage?
The data I need to convert is always actually a number. i.e. even though it is recognised as alpha numeric, the figure is a number.
I just need it to be converted to an integer so i can SUM it etc.
I have a column in table which stores the actual signature from the singature pad. In the table it is stored as varchar(max) datatype and sample data looks like below. Below data is the actual signature of the person.
CREATE
TABLE #test
([Signature]
VARCHAR(MAX))
[code]...
How do i decrypt this data so i can print actual signature on my report.
I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
Hi ,
I am Using Derived column between Source and Destination Control. the Source input column PriceTime is String Data type. but in the Destination is should be a DATE TIME column. How to Convert this string to DateTime in the Derivied Column Control.
I already tried to in the Derived column control
PRICEDATETIME <add as new column> ((DT_DBTIMESTAMP)priceDateTime) database timestamp [DT_DBTIMESTAMP]
But still throwing Error showing type case probelm
Pls help me on this
Thanks & Regards
Jeyakumar.M
I want to select weekly data from daily data.lets say Today's date-10/23/2014(Thursday) My data is in date time but i want to see only date
output should be from last week Thursday to this week Wednesday. similar for previous dates
Weekly sum(profit)
10/16 - 10/21 - $1000
10/9 - 10/15 - $4100
10/2 - 10/8 - $ 8038
--
--
--
I have 2 tables in my database.
one is Race table and 2nd one is Age Range.
I want to write a query where I can see all races and age range as column.
TblRace
ID, RaceName
TblAgeRange
ID,AgeRange.
There is no connection between this two table. I need to display result like below.
Race 17-20 21-30 31-40
A
B
I
W
How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.
ItemID ItemName Price
1 Test 1 100
1 Test 1 200
2 Test 2 130
2 Test 2 150
3 Test 3 300
3 Test 3 400
...
How to write a query to bring price data to column so the Item is not duplicate any more.
The result should be:
Item ID ITemName PRice1 PRice2
1 Test1 100 200
2 Test2 130 150
3 Test3 300 400
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
While working with data comparison I faced an issue of mismatched data instead of having same data. Below is Script
CREATE TABLE #TestAA(
[ID] [int] NOT NULL,
[value] [float] NOT NULL
)
CREATE TABLE #TestBB(
[ID] [int] NOT NULL,
[value] [float] NOT NULL
[Code] ....
Or you can use
SELECT ID ,value FROM TestAA
except
SELECT ID, value FROM TestBB
How to split a column data into multiple rows, below is the requirement...
Create table #t3 (id int, country (varchar(max))
INSERT #t3 SELECT 1,' AU-Australia
MM-Myanmar
NZ-New Zealand
PG-Papua New Guinea
PH-Philippines'
Output should be like below
1 ,AU-Australia
1,MM-Myanmar
1,NZ-New Zealand
1,PG-Paua New Guinea
1,PH-Phlippines
Note: we are getting source data from sqlserver tables.
I googled and found below way but did't get the output as required
SELECT A.id, a.country,
Split.a.value('.', 'VARCHAR(500)') AS String
FROM (SELECT id, country ,
CAST ('<M>' + REPLACE(country, ' ', '</M><M>') + '</M>' AS XML) AS String
FROM #t3) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
I have an SSIS package that pulls data from a MYSQL DB (Using RSSBus for Salesforce in SSIS to accomplish this). Most of the columns are loading properly, but I have many columns that I need to convert.
I have been using the Data Conversion dataflow task in SSIS to convert the rows.
I have 2 data conversions that work on most of the columns, but the DESCRIPTION column continues to return an error saying "Cannot convert between unicode and non-unicode types", regardless of what I choose on the Data Conversion task. So, basically I want to dump this column data into a SQL table with NVARCHAR datatypes. Here is what I am doing in my SSIS package...
1) Grab subset of data from SOURCE
2) Converts to TEXTSTREAM. (Data Conversion)
3) Converts to STRING. (Data Conversion)
4) Load Destination table. (OLE DB Destination)
I have also tried to simply convert the values to STRING, but that doesn't work either.
So, I have 2 Data Conversions working here that process most of the data correctly. What I can do to load the DESCRIPTION column?
We have a typical issue with Column Store Index, we have a procedure which does 2 activities - Switch & Reverse Switch
Switch:
1. Fetch the Partitions needed to be switched
2. Switch the data from Main Table to Switch table
2. Disable the Column store on Switch table
SSIS Package:
3. Load data to Switch (Insert / Update)
Reverse Switch:
4. Enable the Switch
5. Switch back the data from Switch table to Main table
Issue: Some time the Column store is not getting disabled, and the package fails complaining try disabling the Column store index and try loading data.
If we re-run the procedure, the column store gets disabled.
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
View 1 Replies View RelatedI am following the SSIS overview video- URL...I have a flat file that i want to import the contents onto a SQL database.I created a Dataflow task, source file and oledb destination.I am getting the folliwung error -"column "A" cannot convert between unicode and non-unicode string data types".in the origin file the data type is coming as string[DT_STR] and in the destination object it is coming as "Unicode string [DT_WSTR]"I used a data conversion object in between, dosent works very well
View 5 Replies View RelatedI need to create SQL to convert multiple rows data to single row for given subscriber#. Below is the example. In below example , I've 4 family members with same subscriber # and each members have separate rows, I want to combine member data for same subscriber in 1 row, so there would be a 1 row for each subscriber.
View 6 Replies View RelatedMonth Discount
Expenses GST
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d14582be-eecd-4def-be7e-0cfe00b13c80" id="4279c12f-e03f-4b38-9fc8-eb1a991c25ac"><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7f0da910-3775-4ee4-821e-1a0e7ee2ce0b"
id="92ce074f-ee0b-4794-839b-ee50c88d380c">ServiceCharge</gs></gs>
[Code] ....
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,
[Code] ....