Transact SQL :: How To Convert Row To Column In Server
May 11, 2015
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
View 9 Replies
ADVERTISEMENT
May 29, 2015
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 .
View 2 Replies
View Related
May 20, 2015
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.
View 2 Replies
View Related
May 21, 2015
I want to convert row to columns with rows dynamic. I have two table, I'll join them and get data. See at
Ok , I try convert rows to columns with PIVOT . i want the result as
GROUP_MOD_IDADDEDITDELETE
G02101
CREATE PROC GET_FUNC @GID CHAR(5)
AS
BEGIN
SELECT FUNCTION_MOD_NAME
[Code] ....
But it's error
Msg 156, Level 15, State 1, Procedure GET_FUNC, Line 13
Incorrect syntax near the keyword 'FOR'.
Note: Type of ALLOW is bit and rows FUNCTION_MOD_NAME is dynamic
View 2 Replies
View Related
Jul 9, 2015
I have a table with 2 columns and my source data looks like this..
PolicyNum InsCode
1ABC12 1001
1ABC12 1002
1ABC12 1003
1ABC12 1004
1ABC12 1005
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum InsCode1 InsCode2
1ABC12 1001 1005
1ABC12 1002 1006
1ABC12 1003 1004
1ABC20 1001 1005
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005
InsCode value 1002 should always mapped to 1006
InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
[Code] ....
View 14 Replies
View Related
Jul 27, 2015
I have to varchar columns that contain date and time columns.
Date Time
22-06-2015 12:28:29
My output that I want:
Date
2015-06-22 12:28:29
Is there a function that I can convert the date format like I want.
View 8 Replies
View Related
Jul 29, 2015
This is a string i am getting from sql server.
00000006637120150522187449637100 34
10-000000003444378351108502007
01016800002413
10-000000091541378538466562009
01016800002420
[Code] .....
View 9 Replies
View Related
May 27, 2015
We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.
exec sp_trace_setevent @TraceID, 20, 23, @on
exec sp_trace_setevent @TraceID, 20, 8, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 64, @on
exec sp_trace_setevent @TraceID, 20, 1, @on
exec sp_trace_setevent @TraceID, 20, 21, @on
[code]...
View 6 Replies
View Related
Feb 17, 2015
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
View 9 Replies
View Related
Jul 8, 2015
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'
View 9 Replies
View Related
Apr 30, 2015
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
NUMERIC(8,0) CCYYMMDD?
I tried this...
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
but that put it in the format of...
AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''
Which is close...I think I just need to lose the "-"
View 5 Replies
View Related
Mar 5, 2007
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.
Any help in this will sincerely be appreciated.
Thanks
View 7 Replies
View Related
Jul 8, 2015
I made a select query which shows following output as shown in picture .
Now I want to add one more column in this query to show current bags and Bags in these 2 columns i want to show calculation like in first rows currentbags column (Receivedbags-DeleiveredBags) and in currentWeight column RecivedWeight+loss-gain-Deliverdweight) which is 1400 and 697.5 after that in secound row i want to add frist rows currentbags value+ second rows (Receivedbags-DeleiveredBags) and same in weight like daily stock register so output looked like below image
There is one more column common date according to which i have to make calculation like
rid commondate recdate recbags recweight loss gain delbags delwght
101215109 01/01/2015 07/01/2015 1400 697.5 0 0 0 0
101215110 02/01/2015 08/01/2015 560 279.64 0 0 0 0
View 7 Replies
View Related
Oct 20, 2015
We have to encrypt one of the SQL Server column data at the database level, we don’t want to modify the application programs, because we use ERP application and we don’t want to reapply our customization very few months, so we are looking for an option to encrypt and decrypt the column level data value using trigger/procedure/function, I used instead of trigger on that table to encrypt and decrypt the values but it is not working, we want only one user(Application Service account) have an ability to encrypt and decrypt the data, not for any other id.. I read somewhere we cannot automatically encrypt/decrypt the column value using trigger, is that true...
View 5 Replies
View Related
Jun 30, 2015
I have these values
100
900
1300
in int format , now i want to convert them to hh:mm:ss tt format in sql. How can this be done ? I have used this query
declare @nvsDateTime int;set @nvsDateTime = 100;
select convert(datetime, convert(varchar(10),CURRENT_TIMESTAMP,120) + ' ' + stuff(ltrim(@nvsDateTime),3,0,':') ) AS Answer
But it is only converting 100 and 1300 but not 900 and giving me this error :
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
View 7 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Aug 5, 2013
I have this datetime: '2002-12-20 11:59:59'
I want to convert this to date (yyyy-mm-dd) to integer
This works fine for: SELECT CONVERT(INT, GETDATE())
But it doesn't work here: SELECT CONVERT(INT, '2002-12-20 11:59:59')
I want to convert date to integer without passing through any varchar conversion/result.
View 6 Replies
View Related
Nov 27, 2015
Having an issue getting this done. The datatype in the table is varchar(50). The values in the table are
11122015
11122015
11122015
Nothing has worked so far.
View 3 Replies
View Related
Oct 8, 2015
How can I convert the following data
Create Table tbl_Customer_Bank_Details
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo] varchar(50)
)
[Code] ...
into the following format
Create Table tbl_Customer_Bank_Details2
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo1] varchar(50),
[Code] ....
I am looking at this [URL]
how to do this in SQL 2000.
View 16 Replies
View Related
Apr 24, 2015
i have a table with dob and test results , i am trying to pull the data from the table and converting rows columns , below is the table i am using . i used to pivot to do this .
create table #TEST_RESULTS
(ID INT,NAME VARCHAR(10),DOB DATETIME,DAYS_SINCE_BIRTH_TO_TEST INT,TEST_RESULTS INT )
INSERT INTO #TEST_RESULTS
VALUES(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 1 ,3)
,(1,'A','2015-01-01' , 2 ,6)
[code]...
View 9 Replies
View Related
Jul 28, 2015
I have below string, which is a datetime value
I want to convert it into datetime data type and insert into table
DECLARE @Date VARCHAR(100)
SET @Date='10312013122642'
View 8 Replies
View Related
Oct 6, 2015
I have a column type of float and How to convert it show like this
default value =39260.80 MY db use this ',' seperator instead of '.'
wanna convert 39,260
This code is working if using '.' seperator
SELECT parsename(convert(varchar,CAST(floor('39260.80') AS MONEY),1),2)
not work using ',' seperator
SELECT parsename(convert(varchar,CAST(floor('39260,80') AS MONEY),1),2)
View 12 Replies
View Related
Jul 23, 2015
In y sql server table has millions of records available. I don't want to drop the tables.
My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.
Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !
View 13 Replies
View Related
Aug 20, 2015
I have a data column coming in from a 3rd party vendor in the format of CCYYMMDDHHMMSS. How can I convert this data to a [datetime] format?
CAST(CAST([TransactionDate] AS CHAR(14)) AS datetime)
Is that correct?
View 7 Replies
View Related
Oct 13, 2015
I have a table that records a GL period as a string. I want to convert this value to the last date in the month.
GlPeriodID
APR 2015
I want to convert to
4/30/2015
View 7 Replies
View Related
Sep 19, 2015
Select DATEPART(year, OrderDate) As Years,SO.TotalRevenue
From Sales.Orders S
Cross Apply (Select Sum(SD.Qty*SD.unitprice-SD.discount) As TotalRevenue From Sales.OrderDetails SD where S.orderid = SD.orderid
) SO
Group by DATEPART(year, OrderDate),SO.TotalRevenue
View 7 Replies
View Related
Sep 21, 2015
I'm developing one leave application form in that I've declared "No of days"column as nvarchar type..If the user taking half(1/2) day leave then have to covert that 1/2 into 4 hours itseems..since I'm trying to display the table data on datagridview while displaying that half day in gridview again it has to conver 4 hours into 1.2 format..
My Table design:
Create table Leave_Form
(
Employee_id int primary key,
Emp_Date date,
Emp_name nvarchar(50),
[Code] ....
Windows form:
View 8 Replies
View Related
May 14, 2015
I have a text field which displays quarter using YYYYQ (i.e. 20142, 20151, etc...)
I need to convert the field to a datetime representing the last day of that quarter. For example 20142 would be 06-30-2014 and 20151 would be 03-31-2015.
View 7 Replies
View Related
Jul 7, 2015
In the database, most of our cities are stored in all upper case. For reporting purposes, I need to have them returned as upper/lower. I used the below function, which works great for one word cities. However I can’t figure out how to get it to capitalize the 1st letter of each word, for addresses containing multiple names such as Rancho Santa Margarita.
Upper(left(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY,1))+lower(substring(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY, 2, LEN(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY)))As ADDRESSCITY
This returns back: ‘Rancho santa margarita’; I need it to return ‘Rancho Santa Margarita’. Is this possible to do at the query level?
View 10 Replies
View Related
Oct 14, 2015
I have a table that holds full names but it's lastname, firstname.I need to select and convert to firstname lastname and remove the comma.
View 7 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Sep 16, 2015
I have a datetime field for e.g 2015-09-15 00:00:00.000 and an integer field for e.g.100809.
The integer field is actually a time value as in hours,minutes,seconds. For e.g. the value
100809 means
10-hours
08-minutes
09-seconds
I need to combine the datetime and the int field to get the output as below
2015-09-15 10:08:09
View 7 Replies
View Related
Oct 19, 2015
In our application, we do scheduling. I need to convert the entire scheduling functionality into Transact-SQL for a report. I'm stumped on how to convert some of that functionality. We use a function from Delphi that I see no equivalent in SQL. Further, we still support SQL2005, so the Transact-sql has to be compatible with SQL2005. So, DateFromParts will not work at all for me as it was added in a later version.
The function looks like this:function EncodeDayOfWeekInMonth(const AYear, AMonth, ANthDayOfWeek, ADayOfWeek: Word): DateTime;
The purpose of the function is:Returns a DateTime that represents a specified occurrence of a day of the week within a specified month and year.how I can do this with a Transact-sql statement? As I said earlier, I'm constrained by the fact it must work in a SQL2005 instance.
View 4 Replies
View Related
