Making Single Column Value To Multiple Columns
Mar 4, 2008
I have a table which has single column like this.
REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT
I want to put this in a new table in individual columns
Col1 col2 col3 col4 col5 col6 col7 col8 col9
REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT
How to do this.
Thanks.
View 2 Replies
ADVERTISEMENT
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Jun 4, 2015
I have a requirements to make a single column using a date from multiple columns. below my DDL and sample.
Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)
[code]....
View 10 Replies
View Related
Mar 9, 2008
I have FirstName,LastName columns in the database.I need to return FirstName,LastName as Name to client(as a single column).
View 3 Replies
View Related
Oct 8, 2007
Suppose that I have a table with following values
Table1
Col1 Col2 Col3
-----------------------------------------------------------
P3456 C935876 T675
P5555 C678909 T8888
And the outcome that I want is:
CombinedValues(ColumnName)
----------------------------------------------
P3456 - C935876 - T675
P5555 - C678909 - T8888
where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-'
So is there any way to achieve this?
View 1 Replies
View Related
Mar 3, 2015
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
For Eg:
Description
APPEARANCE UNIT
BDV KV
DENSITY KG/L
View 9 Replies
View Related
Apr 12, 2007
I hope i'm in the right place, but thanks anyway....
Actually i have 2 questions (regarding sql-server Indices/Keys):
1) I have an index, which is consisted of 4 columns.
I've read elsewhere that this index functions (as well) as an index (single column
index) on the first column of this multi-column index.
Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns
seperately i need to define only 3???
2) I have a unique key consisted of multiple columns.
I'd like to save an index to this combination of columns as well (to speed up
things in DB...).
Does the definition of a multiple-columns key free me from defining the multiple-
columns index???
can anyone explain the main diference between Keys and Indices???
View 1 Replies
View Related
Apr 16, 2007
I hope i'm in the right place, but thanks anyway....
Actually i have 2 questions (regarding sql-server Indices/Keys):
1) I have an index, which is consisted of 4 columns.
I've read elsewhere that this index functions (as well) as an index (single column
index) on the first column of this multi-column index.
Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns
seperately i need to define only 3???
2) I have a unique key consisted of multiple columns.
I'd like to save an index to this combination of columns as well (to speed up
things in DB...).
Does the definition of a multiple-columns key free me from defining the multiple-
columns index???
can anyone explain the main diference between Keys and Indices???
thanks,
Ran Kizi
View 3 Replies
View Related
Oct 19, 2015
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
select
t1.Code1TypeId + ';' +
t1.Code2TypeId + ';' +
t1.Code3TypeId + ';' +
t1.Code4TypeId as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
View 4 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Jun 15, 2006
Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like thisThe query looks like thisSelect ID, TYPE, VALUE From myTable Where filtercondition = 1ID TYPE VALUE1 type1 121 type2 152 type1 16 2 type2 19Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returnsID TYPE1 TYPE2 VALUE1 VALUE21 type1 type2 12 152 type1 type2 16 19Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.
View 1 Replies
View Related
Mar 31, 2008
Running SQL2000
I have a one to many relationship between Flag and StudentFlag. I'm wanting to select each Flag.FlagID that exist as a column that contains the StudentFlag.Value per StudentFlag.StudentID (or null for that column if that StudentFlag row doesn't exist).
This single column appearing multple times in a single dataset has seemed difficult in a select statement. Is it posible in T-SQL?
Tables, Keys, and Relationships
CREATE TABLE [dbo].[Flag] (
[FlagID] [int] NOT NULL ,
[FlagName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[StudentFlag] (
[StudentFlagID] [int] NOT NULL ,
[FlagID] [int] NULL ,
[StudentID] [int] NOT NULL ,
[FlagValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Flag] WITH NOCHECK ADD
CONSTRAINT [PK_Flag] PRIMARY KEY CLUSTERED
(
[FlagID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StudentFlag] WITH NOCHECK ADD
CONSTRAINT [PK_StudentFlag] PRIMARY KEY CLUSTERED
(
[StudentFlagID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StudentFlag] ADD
CONSTRAINT [FK_StudentFlag_Flag] FOREIGN KEY
(
[FlagID]
) REFERENCES [dbo].[Flag] (
[FlagID]
)
GO
Sample Output
StudentID, FlagType1, FlagType2, FlagType3
1, 'yes', '2', null
2, null, null, 'X'
3, null, '7', 'X'
View 4 Replies
View Related
Apr 25, 2014
I have this query
SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END
FROM Table1 WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code
and the output
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 0.00 0.00 0.00
Type 0.00 4749072.19 0.00 0.00
Type 0.00 0.00 149214.04 0.00
Type 0.00 0.00 0.00 792210.10
How can I modify the query to come up with output below,
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 4749072.19 149214.04 792210.10
View 9 Replies
View Related
Feb 27, 2008
Hello,
I need to concatenate a column from multiple rows into a single column in a new table.
How can I do this?
SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR
Need to have
SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR
THANK YOU
View 10 Replies
View Related
Jan 13, 2015
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
View 4 Replies
View Related
Sep 10, 2014
I’m trying to figure out a way to sum columns for similar IDs, based on the contents of a single field. For example, if I’m calculating attendance percentages for students, and codes P and T count as Present, and codes A and E count as Absent, I would want to total Present and Absent codes separately, in their own columns. I would then like to use those totals to calculate percentage, but I can do that. It’s the SUM based on column value (by ID) that is giving issue.
If I have the following view:
IDLNFNCDTotal
123456MearsBenP12
123456MearsBenA2
123456MearsBenT6
234567NortonSusanP15
234567NortonSusanA2
234567NortonSusanT2
234567NortonSusanE1
I would like something like this:
IDLNFNPresentAbsentPercentage
123456MearsBen18290.0
234567NortonSusan17385.0
I’ve been playing around with nested queries, but nothing’s working. This is a glimpse of the mess that I’ve created trying to sort this out. Many errors.
I just noticed that I used a simpler example than the SQL I included, so I modified it a bit. There are additional fields that I'll need to include, but I want to get the logic working correctly. From there, I can handle the rest. So here's a more appropriate code example showing the direction I'm trying to go with this.
SELECT ID, [Last Name], [First Name], CD, Present, Absent, CAST(LEFT(Present / (Absent + Present) * 100, 5) AS varchar) + '%' AS Percentage
FROM (SELECT ID, CD, TotalAHD, CAST
((SELECT SUM(TotalAHD) AS Expr1
FROM SumAHDforAttndPercentages AS p
[Code] ...
View 2 Replies
View Related
Jul 28, 2015
So I have a problem with combining cells: some of the cells are blank and some of the cells have NULL value.
The combined blank cells return perfect.
But the combined cells in which 1 or more contains NULL will return as NULL
0-4139-00-01OPTIONAL EQUIPMENT 139 - A -
0-4139-00-02NVG EQUIPMENT 139 - A -
0-4139-00-03NVG EQUIPMENT 139 - A -
0139-01-01BRAKE RESERVOIR NULL
A part of the script which i use is:
STUFF((SELECT ', ' + i.ac_type + ' - '
+ i.range_or_single+ ' ' + ' - ' + i.serialno_to
FROM msc_item_effectivity i
inner JOINmsc_item h
ONb.taskcard_verno_i = h.taskcard_verno_i
WHERE h.itemno_i = i.itemno_i
FOR XML PATH('')), 1, 1, '')
i.ac_type = 139
i.range_or_single = A
i.serialno_to = blank or NULL
I want the result of the blank value to be the same as the NULL value.
0139-01-01BRAKE RESERVOIR 139 - A -
View 2 Replies
View Related
Dec 19, 2006
Hi:
I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks
MA2005
View 9 Replies
View Related
Jan 13, 2005
Hi
This is probably a very basic question for most people in this group.
How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure.
Here is an example of what i want to acheive
FName
John?Doe
FName LName
John Doe
thanks for the help
prit
View 7 Replies
View Related
Nov 9, 2006
hi,
i am having 2 columns in a table in a dataset.
i want to add those two columns and bind the resultant total as a single column to the datagrid.
is it possible.
if yes, how o acheive this?
please help me.
thanks in advance.
muppidi.
View 1 Replies
View Related
May 10, 2001
Hi,
i need to select from a table transact where one of the coulmn values has to be equal to (1and 2 and 3).
e.g: column in (1,2,3) would give me what "OR" would do,
View 1 Replies
View Related
Sep 3, 2014
I have data:
Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1
Essentially if Ticket and User are the same I just want the min priority returned.
SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1
I've tried partition and rank but can't get it to return the right output.
View 5 Replies
View Related
Jun 25, 2004
how does one specify multiple values for a single column in a where clause?
example:
SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')
if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:
View 2 Replies
View Related
May 5, 2008
It is possible to alter multiple columns within a single alter table statement?
I have got the following URL that tells it is not possible to alter multiple columns within in signle alert table statement.
http://www.blogcoward.com/archive/2005/05/09/234.aspx[^]
Does anyone know about that?
Thanks,
Mushq
View 4 Replies
View Related
Jul 8, 2014
With the below query iam able to retrieve all the tables invloved in a stored proc. But, what I want to display the table names as comma separated list for each table.
;WITH stored_procedures AS (
SELECT o.id,
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT id,proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name
View 6 Replies
View Related
Sep 17, 2015
How do i concatenate 24 columns with semicolon delimited into a single column without getting data conversion error ....
View 2 Replies
View Related
Aug 28, 2007
I have embedded a subreport in the detail section of my report. When I view the subreport in page preview mode it renders with three columns, but when it is rendered w/in the main report in page preview mode the 3 column layout is rendered as a single verticle column.
I've defined the columns in the Body/Columns. I have also set the report inteactivesize and pagesize heights in an attempt to remedy the issue. Margins are set to 0.
Any help is appreciated.
View 3 Replies
View Related
Apr 6, 2014
I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.
Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.
EXAMPLE: All in one row.
ProgramID | AttributeNames
887 | Studydesign, Control Groups, Primary Outcomes.
I have attached an image of the SQL VIEW that I need to modified so it does this.
THE QUERY:
SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID,
dbo.tblAttributes.AttributeName
FROM dbo.tblProgramAttributes INNER JOIN
dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID
WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%')
ORDER BY dbo.tblProgramAttributes.ProgramID DESC
View 5 Replies
View Related
Sep 4, 2014
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
View 2 Replies
View Related
Jun 21, 2012
I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<
View 9 Replies
View Related
Feb 12, 2015
I am looking a script which allow me add single coilumn to multiple table of my database.
For Example :-
I am having 4 table
1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table
Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.
Alter table EMP
Add Rowchecksum varbinary(8000)
View 1 Replies
View Related
Oct 14, 2015
LeaveEntitlementID PeriodID LeaveType EmployeeID NumberOfDays
1 1 Annual 1 10
2 1 Annual 1 10
3 1 Sick 2 10
4 2 Sick 2 10
5 2 Sick 2 10
I have the above table (LeaveEntitlement) which has the above columns.
What I want to sum the column NumberOfDays based on EmployeeID, LeaveType and PeriodID columns as of LeaveTypeNumberOfDays.
For example sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Annual
The result should be shown in new column name AnnualLeave (20)
sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Sick
The result should be shown in new column name SickLeave (10)
Same all leave Types
The table should be shown as the below after executing the query
LeaveEntitlementID PeriodID EmployeeID AnnualLeave SickLeave
1 1 1 20 0
2 1 2 0 10
3 2 2 0 20
is it possible in sql server
View 8 Replies
View Related
