Lack of imagination... perhaps...

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

SQL Server 2005 Forums

Transact-SQL (2005)

Lack of imagination... perhaps...

Author

Topic

jmnobre
Starting Member

1 Post

Posted - 2009-01-13 : 06:40:54

Hi,

more than a doubt, this is a "how can I do it??" question...
Imagine table T, w/ the following rows:

[match_id] [group] [capture]
---------------------------------
1 col id
2 col name
2 size 32
3 col info
3 size 100
4 col flag

And I need to convert this to something like:

[match_id] [column] [value]
---------------------------------------
1 id <null>
2 name 32
3 info 100
4 col <null>

I'm searching for a '1 query' solution (no cursors, no explicit temporary tables, etc)..

I took a look on the PIVOT option (this is 'almost' a pivot from the original), but I can't find an aggregate to work w/ the string...

Any ideas?

Thanks in advance
jmn

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 07:14:39

This is the downside when using EVA.

DECLARE	@Sample TABLE
	(
		id INT,
		grp VARCHAR(20),
		capture VARCHAR(20)
	)

INSERT	@Sample
SELECT	1, 'col', 'id' UNION ALL
SELECT	2, 'col', 'name' UNION ALL
SELECT	2, 'size', '32' UNION ALL
SELECT	3, 'col', 'info' UNION ALL
SELECT	3, 'size', '100' UNION ALL
SELECT	4, 'col', 'flag'

SELECT		id,
		max(case when grp = 'col' then capture else null end) AS [column],
		max(case when grp = 'size' then capture else null end) AS value
FROM		@Sample
group by	id
order by	id

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-13 : 07:17:57

by using pivot ur getting the output
check this once
SELECT MATCH_ID , [COL] ,[SIZE]
FROM IMAGE I
PIVOT
(MAX(CAPTURE) FOR [GROUP] IN ([COL],[SIZE]))PVT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources