Transact SQL :: Table Row Data Show In Column Data
Oct 22, 2015
I have a simple table data i want want to show row data in to column data.
SELECT clblcode,mlblmsg
FROM warninglabels
My expected result will be
0001 0002 0003 0004
------- ------- -------- --------
May Cause.... Important..... Take madi........... Do Not Take.......
View 16 Replies
ADVERTISEMENT
Oct 4, 2015
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
View 4 Replies
View Related
Nov 9, 2015
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
View 6 Replies
View Related
Sep 25, 2015
I am having two table i.e( tbl_oldEmployee , tbl_NewEmployee ),which is having Column name Employee Name and City same in both table but inside column data is different in different table.but i want to view the Employee name and City from tbl_NewEmployee to tbl_oldEmployee which is having EmployeeId common with tbl_oldEmployee extra record also required (i.e tbl_NewEmployee having 6 record and tbl_oldEmployee having 10 record,so i need to display data from tbl_oldEmployee but first 6 record which id match with tbl_NewEmployee id should be replaced and extra data from tbl_oldEmployee also display).
View 3 Replies
View Related
May 29, 2015
I have table with about 10000 rows, there is a column named Raw_XMLData is defined as varchar but data is xml format.
I try to insert into another table, if Raw_XMLData column has is valid xml data?
Is it possible to do in T sql?
View 2 Replies
View Related
May 29, 2015
I have a SQL text column from SP_who2 in table #SqlStatement:
like 1row shown below :
"update Panel set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"
I want to find what table and column names are in the text ..
I tried like below ..
Select B.Statement from #sp_who2 A
LEFT JOIN #SqlStatement B ON A.spid = B.spid
where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns C
ON T.object_id=C.object_id
WHERE T.type='U'
)
Something like this : find the column names and tables name
View 18 Replies
View Related
May 31, 2007
I have one column in a matrix component and it has about 7 items, but the only the items which have values on the page appear at the top of that page.
This is for a labratory so the columns are the different Patient Types and the rows are the different Test Mnemonics. If one of the Patient Types is not used in any of the tests on that page, it doesnt show up. How to I make sure all Patient Types show up on every page?
Thank you all.
View 2 Replies
View Related
Jun 27, 2014
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
View 4 Replies
View Related
Jun 19, 2015
i'm trying to capture data for every hour and would like to display the hours of the day in a column.
select
case when (datepart(hh,calldate) between 0 and 7AM then 1 else 0 end as [12-7AM],
case when (datepart(hh,calldate) = 8 then 1 else 0 end as [8AM]
from table1
where cast(calldate as date) = cast(calldate as date)
My desired result should display
12-7 am
8 am
9 am
10 am
View 4 Replies
View Related
Apr 28, 2008
I'm getting the error message "The parameter is incorrect" when I'm trying to perform "Show Table Data" from the SQL Server Compact Edition" within VS 2008. Any ideas? Thank you in advance,
.
View 6 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 25, 2013
I linked an SQL server table into Access, but when I open the table in Access, no data shows. Do I need to set some permissions on the sql table?
View 1 Replies
View Related
Nov 18, 2015
I have a column with the data as below :-
<Items>
<Item Value="Value1" />
<Item Value="Value2" />
<Items>
How to get this data into seperate columns as
Items
value1
value2
View 2 Replies
View Related
Aug 26, 2015
I want to show this kind of output
UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
View 10 Replies
View Related
Apr 2, 2008
I have a table with a datetime field 'TheDate'. Currently dates are stored as 'mm-dd-yyyy 00:00:00'. Is there a way to get just the month, day and year parts, '01/01/2008' into the field without changing the field data type to varchar? I'm asking because when I do this:
declare @MyDate as datetime
set @MyDate = '04/02/2008 18:00:00'
select substring(convert(varchar,@MyDate,101),1,10)
I get '04/02/2008', but when I do this:
update TheTable
set TheDate = substring(convert(varchar,TheDate,101),1,10)
I'm still getting a date in the format 'mm-dd-yyyy 00:00:00' stored in the table. I'd like to be able to lose the time portion, but I'd like to be able to keep the datetime datatype for date math purposes. Can it be done?
View 4 Replies
View Related
May 19, 2015
I found string from net how to split column data to row
SELECT A.JbIDFull, A.ProdID, A.OrderQty, A.OtherDetails, A.OrderDate, c.Item FROM
(SELECT JbIDFull, DeptID, ProdID, OrderQty, OtherDetails, OrderDate, LamORSteachType, JBID, RMIDs, RMQty
FROM tblJobCardforProduction WHERE JBID = '2' AND DeptID = '3') A
CROSS APPLY dbo.SplitStringNEW(a.RMIDs,';') b
CROSS APPLY dbo.SplitStringNEW(a.RMQty,'/') c
When I apply one cross apply it's working fine but when i apply for one more column is replacing three time one row, this data i want to split RMIDs and RMQty with same JbIDFull
JbIDFull DeptID ProdID OrderQty RMIDs RMQty
PD-May15-00001 3 2044 10000 PROD-00052 0
PD-May15-00002 3 921 1000 PROD-00052;PROD-00383;PROD-00384 500;600;700
View 2 Replies
View Related
Oct 15, 2015
how to split a single column into multiple columns with | delimited. For example I have column old_values in this I have a data with | delimited and I need to split this data into multiple columns.
Old_values
xxx|yyy|zzz|aaa|bbb|ccc|ddd
into
A B C D E F G
xxx yyy zzz aaa bbb ccc ddd
View 41 Replies
View Related
Nov 4, 2015
Is there a way to encrypt 'varbinary' column data?
View 9 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
Jul 22, 2015
I have a sql table like this
event_id timestamp event_name event_score
1 3000 alarm 0.95
10 3500 alarm 0.85
5 4000 alarm 0.93
20 4200 alarm 0.87
30 5000 alarm 0.87
30 8000 alarm 0.97
40 9000 alarm 0.98
13 9700 alarm 0.98
And I am expecting output like this(With multiple rows)
event_id1 event_id2 event_id3 event_name event_score1 event_score2 event_score3
1 10 5 alarm 0.95 0.85 0.93
30 40 13 alarm 0.97 0.98 0.98
Also I have certain conditions like the timestamp difference between event_Id1 and event_Id3 < 3600 eg 1 hour
View 6 Replies
View Related
May 25, 2015
There is a column named Timings in HH:MM:SS format. Datatype of this column is varchar(50).
I want to sum the rows in this column and get the output as one single record.
00:01:06
00:01:16
00:01:04
00:01:24
00:01:13
00:01:06
00:02:21
00:01:16
View 4 Replies
View Related
Jul 14, 2015
I have a table as below and need getting the desired result as below
Col1 Col2 Col3
A B
C
---desired result
t1 t2
Col1 A
Col2 B
Col3 C
[URL]
View 3 Replies
View Related
Aug 1, 2015
i have table that has rows as below
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
[code]...
i want to select the data priority wise. the o/p should look like
e.g first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
View 6 Replies
View Related
Jun 22, 2015
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
PostCode Latitude Longitude
66000/66100 42.696595 2.899370
20251/20270 42.196471 9.404951
Would become
PostCode Latitude Longitude
66000 42.696595 2.899370
66100 42.696595 2.899370
20251 42.196471 9.404951
20270 42.196471 9.404951
View 6 Replies
View Related
Dec 12, 2007
Hi all experts,
I have a result set like:
Project
Milestone
Info
Project1
M1
Info1
Project1
M1
Info2
Project1
M2
Info3
Project2
M1
Info4
Project2
M2
Info5
Project3
M1
Info6
I need to create a report like:
M1
M2
Project1
Info1
Info2
Info3
Project2
Info4
Info5
Project3
Info6
But while I use matrix to build this report, I got the result like:
M1
M2
Project1
Info1
Info3
Project2
Info4
Info5
Project3
Info6
The report will not show the multiple records on the row group "Project" like item "Info2".
After I referred to the similar problem mentioned on this forums, I tried to use "RowNumber("Matrix1_Project")" to resolve it. But still I got the result like:
M1
M2
Project1
RowNumer
Info1
RowNumer
Info1
RowNumer
Info3
Project2
RowNumer
Info4
RowNumer
Info5
Project3
RowNumer
Info6
It would be much appreciated if anyone could give me some hints to complete the report I need.
Thanks in advance,
Steve Wang 2007/12/12
View 5 Replies
View Related
Aug 27, 2015
I am writing a query where I am identifying different scenarios where data changes between one week and the next. I've set up my result set in the following manner:
PrimaryID SKUChange DateChange LocationIdChange StateChange
10003 TRUE FALSE TRUE FALSE
etc...
The output I'd like to see would be like this:
PrimaryID Field Changed Previous Value New Value
10003 SKUName SKU12345 SKU56789
10003 LocationId Den123 NYC987
etc...
The key here being that in the initial resultset ID 10003 is represented by one row but indicates two changes, and in the final output those two changes are being represented by two distinct rows. Obviously, I will bring in the previous and new values from a source.
View 3 Replies
View Related
Oct 25, 2015
I have a temp table like this
CREATE TABLE #Temp
(
ID int,
Source varchar(50),
Date datetime,
CID varchar(50),
Segments int,
Air_Date datetime,
[code]....
Getting Error
Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.
View 4 Replies
View Related
Aug 14, 2015
Below is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
View 5 Replies
View Related
Oct 13, 2015
I am stuck in the following scenario.
Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01
[code]....
View 5 Replies
View Related
Aug 4, 2015
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name
View 10 Replies
View Related
May 30, 2014
i am trying to configure data collector on my server. so i configured data collector on server A and setup on server B. but the "Query statistics collection set" do not show me any data.
i right click and select "collect and upload now " item and get success result for this. but in the report i cant see any data...
also in the log page of data collection i see so many errors with messages like this:
"Failed to create kernel event for collection set: {2DC02BD6-E230-4C05-8516-4E8C0EF21F95}. Inner Error ------------------> Cannot create a file when that file already exists."
i tried some solution like disabling and enabling again, re-configuring, removing and configuring again .... but none of them work right.
View 2 Replies
View Related
Oct 5, 2015
I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.
Actual Data
Expected Result
1000
1-Aug
1000
1-Feb
1000
1-Jul
1000
[code]....
View 3 Replies
View Related
Jul 9, 2015
I have a table with couple of hundred thousand records, i have added a new column to the table named MD_Group.
Now i need this column populated, so that every 10,000 row set in that table gets a numeric (incremental number) assigned starting from 1.
So the first 10,000 rows will have MD_Group = 1, next 10,000 rows will have MD_Group = 2 , next 10K MD_Group = 3 etc.
For testing purpose here is a results table with total of 11 rows where we want data grouped every 3 rows.
MD_Group
CustomerNumber
AmountBilled
1
12
15243
1
1231234
15243
[code]....
-- Create Sample Table
Declare @GroupRelation_Test TABLE (
MD_Group [varchar](20),
CustomerNumber [varchar](20) ,
AmountBilled [varchar](20) ,
[code]....
-- Test data
INSERT INTO @GroupRelation_Test
( CustomerNumber, AmountBilled,MinAmounttBilled)
SELECT'12','15243','' UNION ALL
SELECT'1231234','15243','' UNION ALL
SELECT'463','15243','' UNION ALL
SELECT'442','15243','' UNION ALL
[code]....
View 22 Replies
View Related
