[RESOLVED] Sewing a Record

[Shaggy Hiker]

That ought to be an interesting subject line.

Suppose you perform a query that returns a collection of records. In those records, you care about only a single field, but would like to see all the fields from all the records in a single record. In other words, if the query returns these records:

A
B
C
D

I would like to turn that into a single record that looks like this:

A B C D

This example is a bit misleading. In the first set of records, the field that I want is always the same, so if the records are
the letters, and the field I want is always field N from each record, then the resulting row would really be:

A(N), B(N), C(N), D(N)

This could be done in code fairly easily. In fact, I can add some things to the records such that it would work even better in code, but doing this in code is also going to be fairly slow. What I would have to do would be to create a datatable with a column for each field, then create a datarow and add the contents into each column of the row. What I would like to know is whether or not I could do this in SQL, as I would expect that the SQL solution, if it exists, would be faster?

[techgnome]

Pivot????

		select ROWID, 
		[Col Code] as COLCODE, 
		[Range Code] as RANGECODE,
		[Restriction Code] as RESTRICTIONCODE
		from (select ROWID, ATTRIBUTENAME, VALUE from COLCODE
					union all 
				select ROWID, ATTRIBUTENAME, VALUE from RANGECODE
					union all 
				select ROWID, ATTRIBUTENAME, VALUE from RESTRICTIONCODE) pvt 
		pivot (max(VALUE) for ATTRIBUTENAME in ([Col Code], [Range Code], [Restrictive Code])) as ATTRIBUTEVALUE

I have three unions to pull some data, which gives me potentially three rows for each ID... I then pivot on the ID and select the MAX VALUE for each of the three items I want... the result of the union has three cols, the ID, which is a GUID, the ATTRIBUTENAME which is a text value "Col Code", "Range Code" or "Restriction Code" and the Value, which is also text. The pivot rotates the data so that Col Code, Them Range Code and Restriction Code become columns, the data is rotated on the ID, and the value in each col is the max value for that column by ID ... if that makes sense...

-tg

[smendoza]

Sounds you want to do a pivot table? Or I'm not understanding your question still.

[Shaggy Hiker]

Probably the latter, as it is mighty weird.

The goal is to create a system where tables have arbitrary lengths such that a person can add as many data items to a record as they feel like, regarless of the number of fields in the table. Is that weird enough? I have it pretty well worked out as a table that acts as a heap of loosely tied fields. It could work well in certain types of situations, but I would like to expand the range of scenarios where it would work well, and one way I want to do that would be to turn the heap of fields back into a standard data record. In the heap of fields, each field is a record in a table. This table includes the value of the field, plus a few other fields that identify how the data fits in relationship to other data. For instance, the existing record has a RecordID. All fields from the same virtual record share the same RecordID. Naturally, there can be any number of fields for any one record. Therefore, to turn these fields into a conventional record, it would be necessary to query on the physical table for all records that have the same RecordID. From this set of records, only the value is necessary, so the query would look something like this:

SELECT Value FROM DataTable WHERE RecordID = X

This would return a set of records A-D (to be consistent with the earlier example). I should add that each of these Values will also have a name, which could be a second field in that SELECT query, but it isn't truly important for this question....I think. The point would be to take that set of records A-D and have it return a record that looked like this:

A(Value),B(value),C(value),D(value)

There doesn't have to be any pivoting after that, though I suppose that this is technically a single pivot, so it might be called a pivot table, but it isn't, really.

[techgnome]

So.... wouldn't this work:

SELECT A,B,C,D
from YourTable
PIVOT (max(value) for FieldName in (A,B,C,D)) as pvt

-tg

[Shaggy Hiker]

Beats me, I've never even looked at PIVOT before. I didn't know it existed. That Max(value) concerns me, though.

I guess I have some research to do before I can say anything intelligent about that example. Back in a bit.

EDIT: Yep, that's what I was looking for. Much fiddlin' to do, but I can get where I want to be with that.

[techgnome]

Unfortunately because of the way PIVOT works, it has to be an aggregate function of some kind... in my casse it didn't matter since there was only one combination of ID/Value/Attribute...

-tg