need help with query, reverse transposition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need help with query, reverse transposition

Author

Topic

kowalsky
Starting Member

29 Posts

Posted - 2010-09-13 : 16:59:05

hi all,
I have a table that has the following columns (among other):


TableName ColumnName NewValue
========= ========== ========
Tbl1      Col1       100
Tbl1      Col2       ABC
Tbl1      Col3       NULL
Tbl1      Col1       200
Tbl1      Col2       DEF
Tbl1      Col3       123

What would be a select statement that would bring back the result:


Col1 Col2 Col3
==== ==== ====
 100  ABC NULL
 200  DEF  123

I am trying to reverse a transposition I did on a number of tables that populate a table where all rows are transposed as seen in the first table above.

Thanks,

kowalsky

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-13 : 17:27:31

Is there any other column that would help relate or order the data appropriatly? Meaning, how do you know from the data that ABC is assoicated with 100 and not 200?

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-09-14 : 07:53:01

Check this query only if the Newvalue column is a Numeric datatype

ALTER Procedure DD
AS
BEGIN
/*
DROP TABLE Input
*/
Create Table #Input
(TableName VARCHAR(100),
ColumnName VARCHAR(100),
NewValue INT);

INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl1','Col1',100)
INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl1','Col2',200)
INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl1','Col3',NULL)
INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl1','Col1',300)
INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl1','Col2',400)
INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl1','Col3',123)

INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl2','Col1',1)
INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl2','Col2',2)
INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl2','Col3',NULL)
INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl2','Col1',3)
INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl2','Col2',4)
INSERT INTO #Input (TableName,ColumnName,NewValue) VALUES ('Tbl2','Col3',13)

SELECT TableName, [Col1] AS Col1 , [Col2] AS Col2 , [Col3] AS Col3
FROM
(SELECT TableName, ColumnName, NewValue
FROM #Input ) ps
PIVOT
(
SUM(NewValue)
FOR ColumnName IN
( [Col1], [Col2] , [Col3])
) AS pvt
END

Lets unLearn

kowalsky
Starting Member

29 Posts

Posted - 2010-09-14 : 09:59:11

quote:
Originally posted by Lamprey

Is there any other column that would help relate or order the data appropriatly? Meaning, how do you know from the data that ABC is assoicated with 100 and not 200?

Thanks for your reply,
I am sorry, I missed this one: yes, there is an ID that is common to all "rows" of the transposition (columns in the initial table) that belong to one and only one row.

In my first example, I should have had an additional column ID that has let's say the value of 111 for the first three rows and 112 for the next three rows,
Thanks again,
kowalsky

kowalsky

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-14 : 12:05:32

Google the term EAV (Entity-Attribute-Value)) as it relates to SQL and RDBMS. Pay attention as to why it is a really bad idea.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

kowalsky
Starting Member

29 Posts

Posted - 2010-09-14 : 14:32:39

jcelko,
thanks for the advice.
I googled EAV and I would say calling it a "really bad idea" is a stretch.
There are legitimate reasons where you can/should use this approach; yes, the queries will be more complex but if you don't need your "values" in the WHERE clause, you should be OK.

Lamprey,
I forgot to add one more ID column - the initial table should look like this:

IDTBL ID TableName ColumnName NewValue
===== === ========= ========== ========
1 111 Tbl1 Col1 100
2 111 Tbl1 Col2 ABC
3 111 Tbl1 Col3 NULL
4 112 Tbl1 Col1 200
5 112 Tbl1 Col2 DEF
6 112 Tbl1 Col3 123

thanks,

kowalsky

kowalsky
Starting Member

29 Posts

Posted - 2010-09-14 : 15:10:23

hi again,
I guess this could be a solution:
SELECT A.id,
A.tblname,
A.val AS col1 ,
B.val as col2,
C.val as col3
FROM TBLTR A, TBLTR B, TBLTR C
WHERE
A.id = B.id AND
A.tblname = B.tblname AND
A.id = C.id AND
A.tblname = C.tblname AND
A.colname='col1' AND
B.colname='col2' AND
C.colname ='col3'
Basically you need an inner join for each column, it's going to get bad if you have large tables ... other than that it should be OK.

kowalsky

kowalsky

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-14 : 17:35:33

You can tweak Naveen's SQL like this:

SELECT ID, TblName, [Col1],[Col2],[Col3]
FROM (SELECT ID, TableName, ColumnName, NewValue FROM myTable ) ps
PIVOT (Max(NewValue) FOR ColumnName IN (Col1],[Col2],[Col3])
) AS pvt

And it should give you the results you want.

kowalsky
Starting Member

29 Posts

Posted - 2010-09-16 : 14:40:10

robvolk,
thank you very much, it seems like a much nicer query, however, I guess that's probably just as efficient as the multiple join query that I submitted - I will have a change to test this assumption and I will post a conclusion at that time.
Thanks again,
kowalsky

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources