3

Basically what I am trying to do is transpose every other column to a row with the following columns data beside it.

The source can have only two columns up to one-thousand and millions of rows. I am only doing this on a per-row basis. I have tried doing a "UNION ALL" however this is incredibly slow when dealing with hundreds of columns in a large table.

Possible solutions are using UNPIVOT although every example I can find is doing something different from what I want.

Example: Before Query

Columns...
Apple | Apple Data | Banana | Banana  Data | Cranberry | Cranberry  Data | .... 

Data Returned... 
IOWA_PL_RLA | 1 | IOWA_PL_MATH | 1 | IOWA_SS_RLA 684 .....

Example: After Query

MeasureID  Score
CRCT_PL_RLA   | 1 
CRCT_PL_MATH  | 1 
CRCT_SS_RLA   |684 
.....

Hopefully I have explained myself enough that someone can help as I am only moderately experienced in T-SQL. If more information is needed please let me know.

DeusAphor
  • 259
  • 1
  • 8
  • so is the `measureid` the `apple`, `banana`, etc and the `value` is from the `data` field? Also are the items with the `data` actually named `....Data`? – Taryn Nov 27 '12 at 19:49
  • I hope I understand you correctly; Yes. The data is actually student assessment data. It's a large flat file loaded in to a table as is. We have a measure and then a following column is the score for it. ( Raw Score, Scale Score, Performance Level, etc.. ( Reading, Math, Science, etc..)) – DeusAphor Nov 27 '12 at 19:58
  • 1
    IN your ETL could you not load the table to be a single description / value table rather than the cross tab as is? Could you add a second row of data that shows what you are looking for (and ideally have the two rows of data before / after actually agree) – u07ch Nov 27 '12 at 20:02
  • I follow what you are saying u07ch but it's a bit more tricky. We have a main education database that everything is loaded into after some heavy ETL which is indeed laid out more along the lines of your suggestion. My ultimate goal here is to formulate some easy way for non-technical employees at the department of education to validate that the assessments were loaded correctly from flat file-stage to the end product. – DeusAphor Nov 27 '12 at 20:13
  • 1
    @DeusAphor Is there a way to identify that the data in `apple` relates to the data in `AppleData`? In your sample, we can identify based on the columns names being similar, is that how it is for all of your columns? – Taryn Nov 27 '12 at 20:19
  • @bluefeet Not at all which is why this is so tricky. It varies for each year and each assessment; The column names are often irreverent to the data on the majority of files. If I was doing this in Excel it would be so easy. There is a lot of bureaucracy; both federal and state. It's a long story but irrelevant to the task at hand. – DeusAphor Nov 27 '12 at 20:34
  • You have my sympathy on the mess you have to work with. Unfortunately I think there is going to be no easy solution here and it will likely require some dynamic SQL and accessing the system tables to sort it out. – JNK Nov 27 '12 at 21:32
  • @JNK & Others thank you for your comments. The solution to my problem was solved with some dynamic SQL and Cade Roux's answer. – DeusAphor Nov 27 '12 at 22:33

1 Answers1

13

If you are using SQL Server 2008, I think you should look into using the CROSS APPLY (VALUES) technique:

http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Basically, it goes like this:

SELECT
  x.NewColumn1,
  x.NewColumn2
FROM YourTable t
CROSS APPLY (
    VALUES
        (t.OldColumn1, t.OldColumn2),
        (t.OldColumn3, t.OldColumn4),
        ...
) x (NewColumn1, NewColumn2);

Here's a runnable example: http://sqlfiddle.com/#!3/9a9d2/5/0

Andriy M
  • 76,112
  • 17
  • 94
  • 154
Cade Roux
  • 88,164
  • 40
  • 182
  • 265