Moving Rows To Columns
Jul 20, 2005
I've seen this answered a number of times in the group, but now that I
actually need the answer I don't know where to look or what to put in
the search.
The question is this:
I have a query that produces these reults:
Col1 Col2
A B
A C
A D
B A
B C
B D
I would like take the above reults, manipulate them and get these:
Col1 Col2 COl3 Col4
A B C D
B A C D
Right now I've got a loop through a cursor, which is ok. It works,
but I'm worried about it because if I end up with a large table in the
future, the longer it will take to run the query with the curors.
Can someone help me please - point me to previous answers to this
question?
Thanks,
Jennifer
View 1 Replies
ADVERTISEMENT
Oct 21, 2007
Hi guys,
I have a table with the following structure
ID CodeID Code Desc
1 10 AA AAAAAAA
2 20 BB BBBBBBBB
3 30 CC CCCCCCCC
4 10 DD DDDDDDDD
5 20 EE EEEEEEEEEE
Now I need to create a new table with the following structure
CodeID AA BB CC DD EE
10 AAAAAAAA Null Null DDDDDD Null
20 Null BBBBBBBB Null Null EEEEEEEEE
30 Null Null CCCCCCCC Null Null
I have managed to create a logic that solve this problem by selecting all AA values and insert it to the new table and then using a series of update statements I updated the values of BB, CC, DD €¦€¦ The problem is that I have to do a 30 update statement so the performance is very bad.
Are there any better solution to implement this table using SSIS that could give me a better performance ?
Thanks a lot.
View 1 Replies
View Related
Dec 25, 2005
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.
View 1 Replies
View Related
Aug 5, 2014
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
View 6 Replies
View Related
Jan 24, 2008
I have a report which is a list of items and I display everything about the item. It is great. My report table in the layout tab is simple. Header,Detail,Footer. Each Item has 65 columns. The number of items (rows) vary upon what you want to see. Example data.
Item#, Description, CaseSalePrice, Cost, BottleSalePrice, Discount
123, Grenadine, 100.00, 75.00, 15.50, 2.00
456, Lime Juice, 120.00, 81.00, 17.25, 2.00
There could be 1 item or 4000 items.
What I want to see is.
Item # - 123, 456
Description - Grenadine, Lime Juice
CaseSalePrice - 100.00, 120.00
Cost - 75.00, 81.00
BottleSalePrice - 15.50, 17.25
Discount - 2.00, 2.00
What I am actually doing is running this the top example and saving to excel. Then copying the sheet. Creating a new sheet then doing a paste special transpose and this gives the users what they want to see.
I want to grab that table object in the report layout tab and twist it 90degrees so the header is on the left, detail is in the middle and the footer is on the right. It would be perfect.
The dynamic column need is really the problem here. I never know how many items will be in the report. They all have the same basic information like description and pricing.
I am all out of creative ideas, any help would be appreciated.
View 6 Replies
View Related
Oct 19, 2007
Hi all,
I am currently writing a web service that gets data from a MSSQL2000 DB and I need to get the data to create some drop down lists, but these lists are composed of many concatenated elements. To sort these alphabetically is proving to be a royal pain in the butt as they just come out as they are ordered in the DB.
It would be a whole lot easier if i could just reorder the rows in one relevant table. Does anyone know how to do this or if it is possible to move rows up or down?
Thanks a lot!
Richee.
View 22 Replies
View Related
May 18, 2007
I am new to script components. I would like to make a simple filter that either passes a row through untouched or eliminates it. I have my input and output buffers set the same, and I have it set as asynchronous. Now these are big rows. Is there a painless way to copy all columns from the input to output buffer, or do I have to do a "Output0Buffer.Col1 = Row.Col1" for each column?
View 6 Replies
View Related
Feb 29, 2012
So I just got an email from Production Support saying an hour and a half downtime is unacceptable to move a half billion rows between 2 partitions because I am moving a clustered index and space is a consideration.
I can not use partition switching because the clustered index is changing.
This is what I am doing...
1. I am creating a new table with the new cluster on a new partiton
2. I am moving the records in 5K set based batches by doing a range search on the existing clustered index on the existing table.
3. I then reapply all of the nonclustered index from the original table to the new one.
4. I do a sp_rename swap out.
The same way I have done this many times before. Is there some new secret special sauce (other than partition switching) I can use?
View 14 Replies
View Related
Aug 17, 2006
Dear all,
In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.
During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.
However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.
Hence, I had to create a quite awkward design to enforce the constraints:
1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.
Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.
Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.
Thanks in advance for any advice.
View 6 Replies
View Related
May 10, 2007
hi,I'm building a multi-lingual website In my database tables I have, in some of them, a column with the Language, because some of the columns depend on what language the user wants to see the site.My question is: what is better? have that column and consequently two row (for two languages) with repeated column information? or have two column within a row with the language specification?e.g. table: id, description, price(1) With language:id,description, price, language='EX' id,description, price, language='EN' vs.(2) id, descriptionEN,descriptionEX,price if I have 500 products in 1 whould result in 1000 entriesin 2 just 500 results can anyone tell me a diference/advantage between the two approachs?thanks in advance.
View 1 Replies
View Related
Oct 6, 2005
is it possible to write a query so that we can have all rows of one column in a single columnTIA
View 1 Replies
View Related
Dec 5, 2005
I am building a calendar table for the most reason four weeks activitis and I have had a temp table data in table A (See my attached file) and I want to
make it as the format in table B as final. How to convert it? Please help!
Thank you!
Suin
View 2 Replies
View Related
Nov 19, 2013
I am using SQL 2008. I have a database called ISCmetrics and a table called Meeting, the table meeting has 5 columns id
( int),TEAMNAME (varchar),DATECOMPLETE (varchar),STATUSNAME (varchar),STATUSLEVEL (varchar)
We have around 20 different team names , and every team enters data into the database every day, and we have around 7 Different STATUSNAME they are always the same , the STATUSLEVEL is always a 1 or a 0 , so TEAMA enters a value every day for each of the 7 STATUSNAME, and the value is either a 1 or a 0 .
The output for a Select * from ISCMetricslooks like this
id TEAMNAME DATECOMPLETE STATUSNAME STATUSLEVEL
1 TeamA 20131022 STATCONTACT 1
2 TeamA 20131022 STATACTION 1
3 TeamA 20131022 STATABC 1
4 TeamB 20131022 STATCONTACT 1
5 TeamB 20131022 STATCTION 0
6 TeamB 20131022 STATABC 1
7 TeamA 20131021 STATCONTACT 0
8 TeamA 20131021 STATACTION 1
9 TeamA 20131021 STATABC 0
10 TeamB 20131021 STATCONTACT 1
11 TeamB 20131021 STATACTION 1
12 TeamB 20131021 STATABC 1
What i am trying to achieve is this, i want the teams in one column then a column for each of the dates , and then sum of the STATUSLEVEL in each row as shown below for the day and team .....
TEAMNAME 20131022 20131021
TeamA 3 1
TeamB 2 3
View 20 Replies
View Related
Jan 18, 2007
hello all,
i am trying to create a view from a table that will keep track of the time between each stage of tasks given. take a look at the data below:
progressID taskIDstage status theDate
------------------------------------------------------------------------
1407525 1091657In Progress Logged 2006-11-16 10:00:24.000
1407526 1091657 In Progress Inprogress 2006-11-16 12:08:59.036
1407214 1091657In Progress Resolved 2006-11-16 14:15:48.000
1407220 1091657Closed Solved 2006-11-16 14:36:05.000
i would like to be able to have just one row per task ID showing the difference between the stages, as shown below . only the 2nd column is a date, the rest are are hours (datediff) between the stage and its preceeding stage :
taskID Logged InProgress Resolved Solved
1091657 2006-11-16 10:00:24.000 2.08 2.07 0.21
is it possible to achieve such a transformation using views and a number of select statements (i.e no dts)? all assistance will be highly appreciated.
regards,
ptah
View 2 Replies
View Related
Jun 9, 2007
Afternoon
I am trying to write a query that will return the columns: year/ month, each status type (unknown how many types there are)
Each row is a different join year/ month
Each cell has the count of users that joined in that rows year/ month and currently have the status of the column.
At the moment I have the following query:
SELECT [remortgage-status].status, COUNT(1) AS CountTotal, YEAR([remortgage-log].datetime) AS Year, MONTH([remortgage-log].datetime) AS month FROM [remortgage-status] INNER JOIN [remortgage-log] ON [remortgage-status].clientid = [remortgage-log].clientid WHERE ([remortgage-log].action = N'Joined') GROUP BY [remortgage-status].status, YEAR([remortgage-log].datetime), MONTH([remortgage-log].datetime)
The problem is that each different status is a new row rather than each status being a column.
What do I need to do to correct this? - I dont know all the different possible statuses at this point
View 9 Replies
View Related
Feb 29, 2008
Hi,
I've a table called months with one column month. The result set will be
month
=====
Jan
Feb
Mar
...
Now i want to convert those values as rows. Means,
Jan Feb Mar
===========
Suggest me a solution for the above problem.
Thanks
Somu
View 2 Replies
View Related
Jul 20, 2005
I know this is a self join, but I can't remember exactly how it goes.Could someone help me out?create table A{int idA,varchar(30) dataA}create table B{int idB,varchar(30) dataB}create table A_B{int idA references A(idA),int idB references B(idB)}insert into A values(1, "foobar")insert into A values(2, "barfoo")insert into B values(1, "a")insert into B values(2, "b")insert into B values(3, "c")insert into B values(4, "d")insert into B values(5, "e")insert into B values(6, "f")insert into B values(7, "g")insert into B values(8, "h")insert into A_B values (1, 1)insert into A_B values (1, 2)insert into A_B values (1, 3)insert into A_B values (1, 4)desired resultsfoobar a b c dThanks,-- Rick
View 9 Replies
View Related
May 26, 2006
Lets say I have the following rows..
ID,Net,Gross,Total
1 ,25.00,55.00,100.00
2,35.00,65.00,250.00
What would be the best way to do this...
ID, Description, Value
1, Net, 25.00
1, Gross,55.00
1, Total, 100,00
2, Net 35,00
I was using the multicast and doing a bunch of derived columns but it seems like there should be a eaiser way to do this.
Any help would be appreciated, thanks.
Mardo
View 1 Replies
View Related
Mar 24, 2008
Hi
i have rows as follows
1 col1 value1
1 col2 value2
1 col3 value3
2 col1 value4
2 col2 value5
2 col3 value6
need result as
id col1 col2 col3
1 value1 value2 value3
2 value4 value5 value6
How to achieve this?
I am trying to achieve this using Pivot, but it is aggregating.
View 8 Replies
View Related
Mar 14, 2007
I have a large amount of data in an XML file. The file is very simple. Off of the root node are category nodes and each category node has many detail nodes. The detail node consist of the node name and the node value. Rather than map the detail to matching named fields in a single record, I would like to send the detail node data to individual data records where the category code, node name and node value are the only fields in the record. For example,
Starting with
<Category>
<Code>001</Code
<Details>
<DetailCode123>75.87</DetailCode123>
<DetailCode728>12.98</DetailCode728>
<DetailCode387>55.72</DetailCode387>
</Details>
</Category>
I want to be able to pick up the 001 code for the category and then transform the above data into 3 separate table records with the following fields (CatCode, DetailCodeName, DetailValue).
001, DetailCode123, 75.84
001, DetailCode728, 12.98
001, DetailCode387, 5572
Does this sort of transform exist? If not, I will have generate my own parsing script. Thought it was worth asking before doing the additional work.
Thanks
View 2 Replies
View Related
Aug 28, 2006
I am converting rows into columns :
Schedule_S1 contains more than 1 rows with different FROM_DATE and TO_DATE
The query should merge 2 rows into 1 row with 2 different FROM_DATE AND TO_DATE
The following query works fine when there are more than 1 records. When there is only 1 record, it should still return the record (with FROM_DATE_2 and TO_DATE_2 as nulls). Thanks for any help.
SELECT
ml1.P_ID,
ml1.S_ID,
ml1.CURRENT_DAY,
ml1.FROM_DATE AS "FROM_DATE_1",
ml1.TO_DATE AS "TO_DATE_1",
ml2.FROM_DATE AS "FROM_DATE_2",
ml2.TO_DATE AS "TO_DATE_2"
FROM
Schedule_S1 ml1 JOIN
Schedule_S1 ml2 ON
ml2.FROM_DATE > ml1.FROM_DATE
WHERE
ml1.P_ID = 59014 AND
ml1.S_ID = 25691 AND
ml1.CURRENT_DAY = 3 AND
ml2.P_ID = 59014 AND
ml2.S_ID = 25691 AND
ml2.CURRENT_DAY = 3
ORDER BY
ml1.FROM_DATE
View 9 Replies
View Related
Jan 3, 2007
I have a query that reports all of the data I want. However it creates about 40 columns and 10 rows. I would like to swap the display so that it shows 10 columns and 40 rows, which would cause it to print out more readable. Can it be done?
View 4 Replies
View Related
Mar 23, 2006
Hello,
I trying to make columns from rows? How do I do that? I am using SQL Server 2000.
I thank in advance anyone capable of helping me.
E. Woods
View 3 Replies
View Related
Aug 14, 2007
I have data in a table. I want the values in the rows to place in columns and columns into rows.
Eg:-A table. It consists of three columns and three rows.
name id dept
a 1 x
b 2 y
c 3 z
I want the resultant table should look like this
a b c
1 2 3
x y z
Whether it's possible ?
View 1 Replies
View Related
Mar 13, 2008
Hi
I have 2 tables defined as follows:
Table1 = uid, Field1, Field2, Field3 ... Fieldn, FormUID
Table2 = FormUID, Label, Position
When I query Table1 I would like to replace the column name of Field1...Fieldn with the Label from Table2 where the Position = n value of Field lable
e.g. lets say Table2 contains the following
1, customerName, 1
1, customerTitle, 2
1, customerDOB, 3
and Table1 might contain
1, Paul Jones, Mr, 21/09/1987, 1
when I query Table1 I would get
uid = 1, Field1 = Paul Jones, Field2 = Mr, Field3 = 21/09/1987
what I would like to get is
uid = 1, customerName = Paul Jones, customerTitle = Mr, customerDOB = 21/09/1987
I have up to 20 Fieldn columns so need to do this for all columns even if there is no matching columns.
any help would be great
regards
View 3 Replies
View Related
Sep 20, 2004
Hi
How can one convert rows into columns (or all rows in one column as a single row, except each row in its own column), either by using a temperary table or just in a select statement?
View 2 Replies
View Related
Apr 14, 2008
I have a table with three columns:
AcctNbr, Type, CodeValue
Listed below is an example of the database.
Code:
AcctNbr Type CodeValue
1 MAILCODE 99
2 MAILCODE 99
3 MAILCODE 99
4 MAILCODE 90
4 MAILCODE 99
4 SEG1 O
5 MAILCODE 99
6 MAILCODE 99
7 MAILCODE 99
8 MAILCODE 99
9 MAILCODE 99
10 MAILCODE 90
11 MAILCODE 99
12 MAILCODE 99
13 MAILCODE 99
14 MAILCODE 99
15 LIST DS1
15 MAILCODE 99
There are multiple Type's for some AcctNbr's and what I want to do is
run a query on the database so that if the AcctNbr has multiple Type's
and CodesValue's it takes them and creates new columns like so:
Code:
AcctNbr MailCode_90 MailCode_99 SEG1
4 90 99 O
So on and so forth. There are multiple Type's and multiple codes that
I need to do this with for each account number. If someone could give
me a base code to try I could start somewhere. I am an SQL novice.
Thanks.
Josh
View 11 Replies
View Related
Oct 22, 2007
Any of you have code to transpose rows into columns in T-SQL 2000. For example I have the following table
ID type
1 A
1 B
2 A
2 C
2 D
etc,....
I want to tranpose
to some like
ID type
1 A,B
2 A,C,D
etc,...
or
ID type1 type2 type3
1 A B
2 A C D
etc,....
Really appreciate any input.
View 3 Replies
View Related
Apr 23, 2008
I have the below table:
Pallet1 ItemA1
Pallet1 ItemB1
Pallet1 ItemC1
Pallet1 ItemD1
Pallet1 ItemE1
Pallet2 ItemA2
Pallet2 ItemB2
Pallet2 ItemC2
Pallet2 ItemD2
Would like to Transpose it to
Pallet 1 ItemA1 ItemB1 ItemC1 ItemD1 ItemE1
Pallet 2 ItemA2 ItemB2 ItemC2 ItemD2 NULL
Would be thankful for Any help.
View 2 Replies
View Related
Jun 22, 2008
hi,
i have the 4rows in one table those are book names...
book1
book2
book3
book4
i have the other table..consisting of usenames
in the output i need like this
username1 book1 book2 book3 book4
username2 book1 book2 book3 book4
View 1 Replies
View Related
Aug 21, 2014
I've a table like below
ColA ColB
A12
B22
C45
A56
A34
B23
and I need to convert the rows into column names like below
ABCA1A2B1
122245563423
View 1 Replies
View Related
Nov 3, 2014
The Source Data is
ID--VAL1--VAL2--VAL3--VAL4--VAL5--VAL6
1---151---- ----- ----- Y----- -----
2---151---- -----Y----- -----Y-----Y
3---152----Y----- ------ ----- ------
4---152---- -----Y------ ----Y------
The Expected Output should be
ID--VAL1--VAL2--VAL3--VAL4--VAL5--VAL6
1--151----- ----Y------Y-----Y-----Y
2--152-----Y-----Y------ -----Y-----
View 1 Replies
View Related
May 7, 2015
I'm using SQL 2008. I want to essentially turn rows into columns. The source table has a variable number of rows and a fixed number of columns - the magical, elusive SQL query will yield a result that has a variable number of columns and fixed number of rows. A slight twist is that there is grouping by Territory, and in this example the first two rows should be reduced to one, with the SlsPerson concatenated to AA/BB.
The table, represented by RC_DataTable:
Territory----State--Est--SlsPerson
----------------------------------
Chicago------IL-----2004--AA------
Chicago------IL-----2004--BB------
New York-----NY-----1989--CC------
Los Angeles--CA-----2007--DD------
The result of the query will yield:
COL1------COL2-------COL3----------
-----------------------------------
Chicago---New York---Los Angeles---
IL--------NY---------CA------------
2004------1989-------2007----------
AA/BB-----CC---------DD------------
Here is a script to establish the above data:
USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RCDataTable](
[code]....
View 2 Replies
View Related
