Hi,
I have a row with 53 columns. All but the first three are of the same datatype.
I want to count the number of columns, out of the last 48 in that row, that have the same value. Is there a nifty way to do this with SQL or T-SQL?
Sample row:
a,b,c,1,1,1,2,1,2,2,2,3,3,3,4,4, etc
The values are not static. I want toknow how many 1's how many 2's etc.
Of course within the app I could for example put the row in a .net datarow and loop through the columns.
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.
I'm using SQL Server 7. I have an invoice table. The invoice table has a datetime column called InvoiceDate. The InvoiceDate column contains the following date format: 5/3/00 I would like to use the InvoiceDate column to update the char (6) column called zInvoiceDate as a formatted date field like yymmdd.
The following syntax did not work: SET zInvoiceDate = Convert([ARInvoiceID],GetDate()12)
In the below query i would like to convert the startdate column as a column i.e i want to display all the days of the month as column and their respective data as a row.
WITH DaysOfMonthCTE AS ( SELECT DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()), 0) AS StartDate , DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()), 1) AS EndDate UNION ALL SELECT DATEADD(day, 1, StartDate) , DATEADD(DAY, 1, EndDate) FROM DaysOfMonthCTE
I thing i have to use pivot transformation in the data flow but i am not sure how to configure this. Can anyone suggest me how to implemet this or configure this!
I want to convert the row value as column name example:--> value ratio 3 4.166666666661 1.315789473680 00 0 To :-> value ratio ratio1 ratio2 ratio3 3 4.166 1.315 0 0 any ideas?
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
HOW DO I CONVERT DATA AND TIME COLUMNS TO DATETIME COLUMN. I HAVE A REQUIREMENT TO FIND OUT THE MAX AND MIN OF DATE AND TIME COLUMNS WHICH ARE TWO SEPARATE COLUMNS ALL TOGETHER. I HAVE DATA IN COLUMN1 AND TIME IN COLUMN2 HAVE TO CONCATENATE BOTH THE COLUMNS TO GET THE MAX AND MIN TO DATE.
EXAMPLE I TRIED TO DO : SELECT MIN(convert(datetime, VH_DATETIME,121), MAX(convert(datetime,VH_DATETIME, 121)) FROM (SELECT TOP 10 INPUT_DATE+INPUT_TIME AS VH_DATETIME FROM ADMINDB.dbo.SSIS_VISIT_HIST) VH
1 W 1.5 100 2 X 1.5 100 3 Y 1.5 100 4 Z 1.5 100 5 W 1.6 98 6 X 1.6 98 7 Y 1.6 98 8 Z 1.6 98.1
Now I want to display the data in this format
ID Color Density Value ID Color Density Value ID Color Density Value ID Color Density Value 1 W 1.5 100 2 X 1.5 100 3 Y 1.5 100 4 Z 1.5 100 5 W 1.6 98 6 X 1.6 98 7 Y 1.6 98 8 Z 1.6 98
Basically I want this format so that I can bind this to the datagridview directly and then use the same for a two way binding (saving) back to the database. How do I get this result format using a query? Thanks in advance.
date item_id grade in out ------ ------- ------- ----- ----- 01-01-08 001 A 10 0 02-01-08 001 O 8 0 01-02-08 002 O 1 0 03-01-08 001 T 0 10 02-01-08 003 O 20 0 02-01-08 003 T 0 10 02-01-08 003 B 0 8
Result View =======
Item_id A B O T Total ------- --- ---- --- --- ------- 001 10 0 8 -10 8 002 0 0 1 0 1 003 0 -8 20 -10 2
I want result group by Item_id and sum of grade in column . where grade could be any Alphabet, the column of result query could varies. Each grade contains sum of (in-out) of item_id of detail table.
I have a simple question to ask; I need to create a column with the data from my DOB column (which has the smalldatetime type attached to it). I know how to do that but I am not too sure how to convert the data from that column int normal character for example when I copy it into my newly created column and change the type to varchar I get this jan 16 1979 from this date 1979/01/16. But I actually want the data to look like this 19790116, so in effect I just want to take out the slashes.
I am thinking that the way to do this would be to convert every 4 bytes into an int and create a temporaty table with an integer column which I populate with int and then run a select * on this temporary table.
Does anybody have any pointers on how I can start to do this?
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 am using SQL server 2008 .I have a table which has 1.5 crore records and some of my columns are Char datatype.Because of this i have spaces in my columns.Now I want to convert char to varchar datatype .But i have index on those columns .
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.
I have a store procedure , a want to convert table result from row to column .My store
CREATE PROC GET_FUNCTION @GROUP_ID CHAR(3) AS SELECT A.GROUP_MOD_ID, B.FUNCTION_MOD_NAME,A.ALLOW FROM FUNCTION_GROUP A INNER JOIN FUNCTION_MOD B ON A.FUNCTION_MOD_ID=B.FUNCTION_MOD_ID WHERE A.GROUP_MOD_ID=@GROUP_ID
And this is my result
Ok , i want to convert same. NOTE : Number of row is can more , G02  Add-Delete   1 , G02   Add-Edit   1 , ..etc. . I want it dynamic column follow row
GROUP_MOD_ID      Add     Edit     Delete      ABC       XYZ    etc....
G02Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â
How can I generate the result using typical SQL statement based on the following tables?
Table a - Salesman (salesId, Name) pk : salesId
Table b - Invoice(InvoiceNo, salesId, InvoiceAmt) pk : invoiceNo fk : salesId -> Table a
The result set :
salesId, Name, sum(InvoiceAmt), InvoiceNos with comma separator
For example:
Table a SalesId Name S001 Peter S002 Alice Table b InvoiceNo SalesId InvoiceAmt INV001 S001 $100 INV002 S001 $100 INV003 S001 $400 INV004 S002 $200 Result set SalesId Name Sum(InvoiceAmt) InvoiceNos S001 Peter $600 INV001, INV002, INV003 S002 Alice $200 INV004
I have a scenario wherein one of the column values in a row contains a string value which is non-delimited (as shown below). I need to split them by 2 characters and generate as many rows as count of set of 2 digits in that string.
I already have a solution in place to run it thru cursor and then do a while loop on the CountyList column by taking 2 digit value using Substring function (keeping start position dyanamic and jumping 2 positions).
Hello Everyone, How can I convert the string column to date in SSIS. Example: I have a column which is having a value as €œ19890213€? Basically this values is a date Now I wanted this value to be a date value as €œ01/01/2007€?
i had try it out by doing this ways (DT_DATE)(SUBSTRING(DATE_SEEN_SPEC,5,2) + "/" + SUBSTRING(DATE_SEEN_SPEC,7,2) + "/" + SUBSTRING(DATE_SEEN_SPEC,1,4))
but still i'm getting the error message.
I know the this can be achived through type cast in Derived Column compunent, but not sure with which cast please help me out in resoulving this issue.
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 need to always have a formatting of 999,999,999.00 in a column called PropertyMap9.
PropertyMap9 is always a nvarchar(max)
How do I SET a value using a CONVERT or CAST to accomplish this ?
Why does this give me a syntax error ? The PropertyMap7 set actually works and performs the calculation and sets the new value for that row, however the next line gives an error. I need all 3 columns PropertyMap9, 8, and 7 to always have the above formatting, while still maintaining the nvarchar(max) datatype in the column. HELP!
update PropertyMapValues
set PropertyMap7 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap8 AS money) - (CAST(PropertyMap9 AS money)),1)
set PropertyMap9 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap9 AS money))
END
Msg 102, Level 15, State 1, Procedure UpdatePropertyMap7, Line 59
Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.
Below is sample of data:
create table #Temp(depID int, Name varchar(50),LocationHead varchar(50)) insert into #temp values(1,'test','head1') insert into #temp values(1,'test','head2') insert into #temp values(1,'test','head3') insert into #temp values(2,'test1','head1') insert into #temp values(2,'test1','head2')
Required output
depID Name LocationHead 1test *head1,*head2,*head3 2test1 *head1,*head2
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
