December 8, 2008 at 12:21 pm
Hi,
I'm a little new to tsql and would like some help with an extract. I have a table that hold data about wastewater assets. The fields are: ufi_id, class_desc, type, attribute_value and attribute_desc. The table holds all wastewater assets and a list of attributes that a particular asset can have. An asset can have upto 50 diiferent attributes and they all vary. I would like to extract this info and have the results per asset on one row.
below is an example of how the date is aetracted (in multiple rows). could anyone please point me on the right path. I have looked at the PIVOT option but don;t think it is going to work/
ufi_idclass type attribute_value attribute_desc
9855Wastewater SystemWWMHOLENULLCONSTCONTR
9855Wastewater SystemWWMHOLENULLRLATBASE
9855Wastewater SystemWWMHOLE1/1/1961CONSTDATE
9855Wastewater SystemWWMHOLE1CONDITION
9856Wastewater SystemWWMHOLE1CONDAS
9856Wastewater SystemWWMHOLE1DROPMH
9856Wastewater SystemWWMHOLE1PERFAS
9856Wastewater SystemWWMHOLE1UNITVALUE
9856Wastewater SystemWWMHOLE3740VAL_DRC
9856Wastewater SystemWWMHOLE4400CRCVALUE
9856Wastewater SystemWWMHOLE4400VAL_RC
9856Wastewater SystemWWMHOLE480MHLIDSIZE
9856Wastewater SystemWWMHOLE660VAL_DIV
I would like the results to be as follows:
ufi_idclasstypeCONSTCONTRRLATBASECONSTDATECONDITIONCONDASDROPMHPERFASUNITVALUEVAL_DRCCRCVALUEVAL_RCMHLIDSIZEVAL_DIV
9855Wastewater SystemWWMHOLENULLNULL1/1/1961112011NULLNULLNULLNULL
9856Wastewater SystemWWMHOLENULLNULLNULLNULL3740NULL44004400480NULLNULL660NULL
Thanks very much for any help. Much appreciated
Greg
December 8, 2008 at 12:40 pm
My attempt at using the pivot command is as follows...
select ufi_id,class,type, [CONSTCONTR], [CONSTDATE]
from
(select * from wastewater) w
PIVOT
( max(attribute_value)
FOR attribute_desc IN
([CONSTCONTR],[CONSTDATE])
) as PVT
ORDER BY ufi_id
December 8, 2008 at 12:43 pm
You could use a pivot or a crosstab query (search for crosstab on this site and look for articles by Jeff Moden) but both require you to essentially hard-code the entire list of columns, so your result set will always have all 50 attribute columns.
If you need the list of columns to vary depending on the attributes the data set you query actually has, you will need to generate your query with dynamic SQL. This is not usually recommended for various reasons, but it may be what you are looking for. Is it?
Here is a good dynamic crosstab query article:
http://www.sqlservercentral.com/articles/cross+tab/65048/[/url]
December 8, 2008 at 12:56 pm
Try this. As pointed out earlier, you could also generate this as dynamic SQL with a MAX() line for every distinct attribute description. This assumes that you will never have two rows with identical attribute descriptions within ufi_id, class, and type.
select ufi_id, class,[type]
,max(when attribute_desc = 'CONSTCONTR' then attribute_value else null end) as 'CONSTCONTR'
,max(when attribute_desc = 'RLATBASE' then attribute_value else null end) as 'RLATBASE'
,max(when attribute_desc = 'CONSTDATE' then attribute_value else null end) as 'CONSTDATE'
,max(when attribute_desc = 'CONDITION' then attribute_value else null end) as 'CONDITION'
--(etc etc for all attribute descriptions)
group by ufi_id, class,[type]
order by ufi_id, class,[type]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 17, 2008 at 1:26 pm
Thanks for the reply guys, much appreciated. Soory but I have been away for a while. I have tried the pivot query,
SELECT ufi_id,ufi_class,ufi_status, [ACCEPTDATE], [ADDRESS1],[ANNDEPR],
, [AREAHA], [AREAM2], [ASBUILT], [AVEHEIGHT], [BANKSLOPE], [BLIFEVALUE], [CCTV], [CCTVDATE], [CLASS], [COMMON], [COMPLEXITY],
[CONDAS], [CONDITION], [CONSENTEXD], [CONSENTISS], [CONSENTREF], [CONSENTVAL], [CONSTAS], [CONSTCONTR], [CONSTDATE], [CONSTDATEA], [CONSTRUCT], [CRCVALUE], [CURDATE], [CURRDATA], [DEPTH] ,
[DEPTHDS], [DEPTHUS], [DESIGNER], [DIAM], [DISTDSMH], [DROPMH], [ENTRYBY], [ENTRYDATE], [FILEREF], [FLOWRATED], [FREQUENCY] ,[FUNCTION] ,[GROUNDL], [HEIGHT], [ILDS], [ILLOW], [ILUS], [INTDIAM], [JOINTTYPE],
[KWCAP], [LENGTH], [MANUFACT], [MATERIAL], [MHDIAM], [MHLIDSIZE], [MHLIDTYPE], [MODELNO], [NAME], [NOTES], [OLDUFI], [OUTLETSIZE], [OWNER], [PCTUTIL], [PERFAS], [PERFORM], [PERIMETER], [PIPEBRIDGE],
[POWERRATE], [REPLACE_DA], [RLATBASE], [SCADA], [SERIALNO], [SHAPE], , [SIZEAS], [STEPS], [STOPBANK], [STREET], [SUPENGINER], [TOPWIDTH], [TOWNSHIP], [TYPE], [UNITVALUE], [UPDATEFROM], [VAL_DATE],
[VAL_DIV], [VAL_DRC], [VAL_RC], [VOLUME], [WALLTHICK], [WAVEBALENG]
FROM
(SELECT * FROM z_UnitedWater_Assets) w
PIVOT
( MAX(attribute_value)
FOR attribute_desc IN
([ACCEPTDATE], [ADDRESS1],[ANNDEPR],
, [AREAHA], [AREAM2], [ASBUILT], [AVEHEIGHT], [BANKSLOPE], [BLIFEVALUE], [CCTV], [CCTVDATE], [CLASS], [COMMON], [COMPLEXITY],
[CONDAS], [CONDITION], [CONSENTEXD], [CONSENTISS], [CONSENTREF], [CONSENTVAL], [CONSTAS], [CONSTCONTR], [CONSTDATE], [CONSTDATEA], [CONSTRUCT], [CRCVALUE], [CURDATE], [CURRDATA], [DEPTH] ,
[DEPTHDS], [DEPTHUS], [DESIGNER], [DIAM], [DISTDSMH], [DROPMH], [ENTRYBY], [ENTRYDATE], [FILEREF], [FLOWRATED], [FREQUENCY] ,[FUNCTION] ,[GROUNDL], [HEIGHT], [ILDS], [ILLOW], [ILUS], [INTDIAM], [JOINTTYPE],
[KWCAP], [LENGTH], [MANUFACT], [MATERIAL], [MHDIAM], [MHLIDSIZE], [MHLIDTYPE], [MODELNO], [NAME], [NOTES], [OLDUFI], [OUTLETSIZE], [OWNER], [PCTUTIL], [PERFAS], [PERFORM], [PERIMETER], [PIPEBRIDGE],
[POWERRATE], [REPLACE_DA], [RLATBASE], [SCADA], [SERIALNO], [SHAPE], , [SIZEAS], [STEPS], [STOPBANK], [STREET], [SUPENGINER], [TOPWIDTH], [TOWNSHIP], [TYPE], [UNITVALUE], [UPDATEFROM], [VAL_DATE],
[VAL_DIV], [VAL_DRC], [VAL_RC], [VOLUME], [WALLTHICK], [WAVEBALENG])
) AS PVT
ORDER BY ufi_id
BUT I get NULL's in my result.
9855WWATERCNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
9855WWATERCNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
9855WWATERCNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
9855WWATERCNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
9855WWATERCNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
9855WWATERCNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
9855WWATERCNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
What am I doing wrong
Thanks
Greg
December 17, 2008 at 2:31 pm
When using PIVOT - you can only specify the columns that you actually are using. If you include additional columns, it causes the problem you are seeing.
So, the following needs to be changed:
FROM
(SELECT * FROM z_UnitedWater_Assets) w
PIVOT
Instead of SELECT * - you need SELECT ufi_id, [ACCEPTDATE], ...
Any other columns not used in the PIVOT - you need to remove.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 17, 2008 at 3:49 pm
Thanks Jeffry. I have changed the script to be:
SELECT ufi_id,ufi_class,ufi_status, [ACCEPTDATE], [ADDRESS1],[ANNDEPR],
, [AREAHA], [AREAM2], [ASBUILT], [AVEHEIGHT], [BANKSLOPE], [BLIFEVALUE], [CCTV], [CCTVDATE], [CLASS], [COMMON], [COMPLEXITY],
[CONDAS], [CONDITION], [CONSENTEXD], [CONSENTISS], [CONSENTREF], [CONSENTVAL], [CONSTAS], [CONSTCONTR], [CONSTDATE], [CONSTDATEA], [CONSTRUCT], [CRCVALUE], [CURDATE], [CURRDATA], [DEPTH] ,
[DEPTHDS], [DEPTHUS], [DESIGNER], [DIAM], [DISTDSMH], [DROPMH], [ENTRYBY], [ENTRYDATE], [FILEREF], [FLOWRATED], [FREQUENCY] ,[FUNCTION] ,[GROUNDL], [HEIGHT], [ILDS], [ILLOW], [ILUS], [INTDIAM], [JOINTTYPE],
[KWCAP], [LENGTH], [MANUFACT], [MATERIAL], [MHDIAM], [MHLIDSIZE], [MHLIDTYPE], [MODELNO], [NAME], [NOTES], [OLDUFI], [OUTLETSIZE], [OWNER], [PCTUTIL], [PERFAS], [PERFORM], [PERIMETER], [PIPEBRIDGE],
[POWERRATE], [REPLACE_DA], [RLATBASE], [SCADA], [SERIALNO], [SHAPE], , [SIZEAS], [STEPS], [STOPBANK], [STREET], [SUPENGINER], [TOPWIDTH], [TOWNSHIP], [TYPE], [UNITVALUE], [UPDATEFROM], [VAL_DATE],
[VAL_DIV], [VAL_DRC], [VAL_RC], [VOLUME], [WALLTHICK], [WAVEBALENG]
FROM
(SELECT ufi_id,ufi_class,ufi_status, attribute_value, attribute_desc FROM z_UnitedWater_Assets) w
PIVOT
( max(attribute_value)
FOR attribute_desc IN
([ACCEPTDATE], [ADDRESS1],[ANNDEPR],
, [AREAHA], [AREAM2], [ASBUILT], [AVEHEIGHT], [BANKSLOPE], [BLIFEVALUE], [CCTV], [CCTVDATE], [CLASS], [COMMON], [COMPLEXITY],
[CONDAS], [CONDITION], [CONSENTEXD], [CONSENTISS], [CONSENTREF], [CONSENTVAL], [CONSTAS], [CONSTCONTR], [CONSTDATE], [CONSTDATEA], [CONSTRUCT], [CRCVALUE], [CURDATE], [CURRDATA], [DEPTH] ,
[DEPTHDS], [DEPTHUS], [DESIGNER], [DIAM], [DISTDSMH], [DROPMH], [ENTRYBY], [ENTRYDATE], [FILEREF], [FLOWRATED], [FREQUENCY] ,[FUNCTION] ,[GROUNDL], [HEIGHT], [ILDS], [ILLOW], [ILUS], [INTDIAM], [JOINTTYPE],
[KWCAP], [LENGTH], [MANUFACT], [MATERIAL], [MHDIAM], [MHLIDSIZE], [MHLIDTYPE], [MODELNO], [NAME], [NOTES], [OLDUFI], [OUTLETSIZE], [OWNER], [PCTUTIL], [PERFAS], [PERFORM], [PERIMETER], [PIPEBRIDGE],
[POWERRATE], [REPLACE_DA], [RLATBASE], [SCADA], [SERIALNO], [SHAPE], , [SIZEAS], [STEPS], [STOPBANK], [STREET], [SUPENGINER], [TOPWIDTH], [TOWNSHIP], [TYPE], [UNITVALUE], [UPDATEFROM], [VAL_DATE],
[VAL_DIV], [VAL_DRC], [VAL_RC], [VOLUME], [WALLTHICK], [WAVEBALENG])
) AS PVT
ORDER BY ufi_id
I now get one row per ufi_id BUT still NULL's
December 17, 2008 at 5:06 pm
What do you want to happen with 'ufi_class' and 'ufi_status'. Those columns cannot be in the PIVOT because they are not being pivoted.
This is one of the problems we have found with using PIVOT. But, it is a small problem :hehe:
If you search this site - you should find an article on cross-tabs. Using a cross-tab instead of the PIVOT statement not only will perform much better, it might even be easier to understand and maintain.
If you really want to use the pivot, remove those extra columns. Then, to get them back into your results you will need to JOIN the table back in on the ufi_id column (if that is the key).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 17, 2008 at 7:49 pm
Jeffry, Thanks for your help. I think I have this sorted. I used this and it has worked. Would just like to replace the word NULL with '' but the data is extracted how I want. Thanks
SELECT ufi_id,ufi_type, [ACCEPTDATE], [ADDRESS1],[ANNDEPR],
, [AREAHA], [AREAM2], [ASBUILT], [AVEHEIGHT], [BANKSLOPE], [BLIFEVALUE], [CCTV], [CCTVDATE], [CLASS], [COMMON], [COMPLEXITY],
[CONDAS], [CONDITION], [CONSENTEXD], [CONSENTISS], [CONSENTREF], [CONSENTVAL], [CONSTAS], [CONSTCONTR], [CONSTDATE], [CONSTDATEA], [CONSTRUCT], [CRCVALUE], [CURDATE], [CURRDATA], [DEPTH] ,
[DEPTHDS], [DEPTHUS], [DESIGNER], [DIAM], [DISTDSMH], [DROPMH], [ENTRYBY], [ENTRYDATE], [FILEREF], [FLOWRATED], [FREQUENCY] ,[FUNCTION] ,[GROUNDL], [HEIGHT], [ILDS], [ILLOW], [ILUS], [INTDIAM], [JOINTTYPE],
[KWCAP], [LENGTH], [MANUFACT], [MATERIAL], [MHDIAM], [MHLIDSIZE], [MHLIDTYPE], [MODELNO], [NAME], [NOTES], [OLDUFI], [OUTLETSIZE], [OWNER], [PCTUTIL], [PERFAS], [PERFORM], [PERIMETER], [PIPEBRIDGE],
[POWERRATE], [REPLACE_DA], [RLATBASE], [SCADA], [SERIALNO], [SHAPE], , [SIZEAS], [STEPS], [STOPBANK], [STREET], [SUPENGINER], [TOPWIDTH], [TOWNSHIP], [TYPE], [UNITVALUE], [UPDATEFROM], [VAL_DATE],
[VAL_DIV], [VAL_DRC], [VAL_RC], [VOLUME], [WALLTHICK], [WAVEBALENG]
INTO z_UnitedWater_Assets_Final
FROM
(SELECT ufi_id,ufi_type, attribute_value , attribute_code FROM z_UnitedWater_Assets) w
PIVOT
(
max(attribute_value)
FOR attribute_code IN
([ACCEPTDATE], [ADDRESS1],[ANNDEPR],
, [AREAHA], [AREAM2], [ASBUILT], [AVEHEIGHT], [BANKSLOPE], [BLIFEVALUE], [CCTV], [CCTVDATE], [CLASS], [COMMON], [COMPLEXITY],
[CONDAS], [CONDITION], [CONSENTEXD], [CONSENTISS], [CONSENTREF], [CONSENTVAL], [CONSTAS], [CONSTCONTR], [CONSTDATE], [CONSTDATEA], [CONSTRUCT], [CRCVALUE], [CURDATE], [CURRDATA], [DEPTH] ,
[DEPTHDS], [DEPTHUS], [DESIGNER], [DIAM], [DISTDSMH], [DROPMH], [ENTRYBY], [ENTRYDATE], [FILEREF], [FLOWRATED], [FREQUENCY] ,[FUNCTION] ,[GROUNDL], [HEIGHT], [ILDS], [ILLOW], [ILUS], [INTDIAM], [JOINTTYPE],
[KWCAP], [LENGTH], [MANUFACT], [MATERIAL], [MHDIAM], [MHLIDSIZE], [MHLIDTYPE], [MODELNO], [NAME], [NOTES], [OLDUFI], [OUTLETSIZE], [OWNER], [PCTUTIL], [PERFAS], [PERFORM], [PERIMETER], [PIPEBRIDGE],
[POWERRATE], [REPLACE_DA], [RLATBASE], [SCADA], [SERIALNO], [SHAPE], , [SIZEAS], [STEPS], [STOPBANK], [STREET], [SUPENGINER], [TOPWIDTH], [TOWNSHIP], [TYPE], [UNITVALUE], [UPDATEFROM], [VAL_DATE],
[VAL_DIV], [VAL_DRC], [VAL_RC], [VOLUME], [WALLTHICK], [WAVEBALENG])
) AS PVT
ORDER BY ufi_id
Regards Greg
December 17, 2008 at 8:33 pm
Very good - glad I could help. To convert the nulls to an empty string ('') - lookup the COALESCE and/or ISNULL functions in Books Online.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply