June 19, 2003 at 1:40 pm
Hi,
I have data in a table like this
create table test (
rowid int not null,
value varchar(50),
caption varchar(20))
insert into test values (1,'100.01','charge')
insert into test values (1,'Pike','lname')
insert into test values (2,'200.01','charge')
insert into test values (2,'chen','lname')
select * from test
rowid value caption
----------- -------------------------------------------------- --------------------
1 100.01 charge
1 Pike lname
2 200.01 charge
2 chen lname
what I want is a query which can return the result this way
rowidcharge lname
----------- ----------------------------
1100.01Pike
2200.01chen
Is it possible without using temp table and cursor, just select statement?
June 19, 2003 at 2:23 pm
try:
select distinct A.rowid, A.value as charge, B.value as lname from
test A inner join test B on A.rowid=B.rowid
and A.caption='charge' and B.caption='lname'
June 19, 2003 at 2:44 pm
Hi, bbychkov
Thanks your response and your query did works. But since this is a ad-hoc query, In advance I did not know th possible value
for caption column. Hopfully there is a solution which did not use 'lname' and 'charge' inside the query. I mean if I put this query inside a procedures, it should work against any data in test table
on different environment.
This must be a big challenge!
June 19, 2003 at 4:33 pm
If I understand it correctly you want to build dynamically a pivot table.
I don't think it can be done with a single SELECT statement.
There is an article in SQL Server Magazine about that:
June 19, 2003 at 4:53 pm
You can use grouping to denormalize more efficiently, like so:
SELECT
MAX(CASE WHEN caption = 'lname' THEN value END) AS lname,
MAX(CASE WHEN caption = 'charge' THEN value END) AS charge
FROM test
GROUP BY rowid
But that doesn't solve your "dynamic" issue, either.
There might be a way of doing it... let me think about it for a while 🙂
Edited by - transio on 06/19/2003 4:53:38 PM
June 20, 2003 at 4:17 am
I have used dynamic sql to create the required query ( as transio listed ) based on the values to be transposed. Still have not found a way to do it in single written sql statement
June 20, 2003 at 7:35 am
Assuming two rows per id,one value numeric, one char and no char value startswith number
then
select a.rowid,a.value,b.value from test a
inner join test b on b.rowid = a.rowid and ascii(b.value) > 57
where ascii(a.value) <= 57
don't know about column headings, have to think about it.
This probably will not do what you want as I predict the data is more complex. Can u give more representative data and structure? Hard to devise a solution if the real intent is not known.
Far away is close at hand in the images of elsewhere.
Anon.
June 20, 2003 at 3:05 pm
Thanks for all your guys response.
Here is the scenario how this query come from
I have an table tbl_primary which has column rowid,field1, field2, where rowid is Primary Key
The users can customize this table by create user defined fields(UDF), but I do not want user to change the schema of tbl_primary, so I created several tables to hold all the user defined columns information ( data type, default etc.). The table test will
hold all the user defined field's name and value for tbl_primary.
In this special case, user defined two columns charge and lname, the caption is the UDF name and value is the value user entered for that column .
Finally, I world like to join tbl_primary and some derived table from test so I can get all the related info
rowid, field1, field2, charge, lname for a specific record.
Users can enter whatever column name and values they needed.
June 23, 2003 at 3:25 am
The only thing I can think of is dynamic sql like this
declare @sql nvarchar(4000)
set @sql='select rowid'
select @sql=@sql+',max(case when caption='''+caption+''' then value else '''' end) as '''+caption+'''' from (select distinct caption from test) a
set @sql=@sql+' from test group by rowid'
exec sp_executesql @sql
but with all the pitfalls (eg injection).
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply