Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>dynamic SQL with PIVOT is the answer. though it is dirty in terms of debugging or say for some new developer to understand the code but it will give you the result you expected.</p> <p>check the below query.</p> <p>in this we need to prepare two things dynamically. one is list columns in the result set and second is list of values will appear in PIVOT query. notice in the result i do not have NULL values for Column3, Column5 and Column6.</p> <pre><code> SET NOCOUNT ON IF OBJECT_ID('TableA','u') IS NOT NULL DROP TABLE TableA GO CREATE TABLE TableA ( stationID int not null IDENTITY (1,1) ,entryDate datetime not null ,waterTemp float null ,waterLevel float NULL ,Column3 INT NULL ,Column4 BIGINT NULL ,Column5 FLOAT NULL ,Column6 FLOAT NULL ) GO IF OBJECT_ID('TableB','u') IS NOT NULL DROP TABLE TableB GO CREATE TABLE TableB ( id int not null IDENTITY(1,1) ,colname varchar(50) NOT NULL ,unit varchar(50) NOT NULL ) INSERT INTO TableA( entryDate ,waterTemp ,waterLevel,Column4) SELECT '2013-01-01',2.4,3.5,101 INSERT INTO TableB( colname, unit ) SELECT 'WaterTemp','celcius' UNION ALL SELECT 'waterLevel','meters' UNION ALL SELECT 'Column3','unit3' UNION ALL SELECT 'Column4','unit4' UNION ALL SELECT 'Column5','unit5' UNION ALL SELECT 'Column6','unit6' DECLARE @pvtInColumnList NVARCHAR(4000)='' ,@SelectColumnist NVARCHAR(4000)='' , @SQL nvarchar(MAX)='' ----getting the list of Columnnames will be used in PIVOT query list SELECT @pvtInColumnList = CASE WHEN @pvtInColumnList=N'' THEN N'' ELSE @pvtInColumnList + N',' END + N'['+ colname + N']' FROM TableB --PRINT @pvtInColumnList ----lt and rt are table aliases used in subsequent join. SELECT @SelectColumnist= CASE WHEN @SelectColumnist = N'' THEN N'' ELSE @SelectColumnist + N',' END + N'CAST(lt.'+sc.name + N' AS Nvarchar(MAX)) + SPACE(2) + rt.' + sc.name + N' AS ' + sc.name FROM sys.objects so JOIN sys.columns sc ON so.object_id=sc.object_id AND so.name='TableA' AND so.type='u' JOIN TableB tbl ON tbl.colname=sc.name JOIN sys.types st ON st.system_type_id=sc.system_type_id ORDER BY sc.name IF @SelectColumnist &lt;&gt; '' SET @SelectColumnist = N','+@SelectColumnist --PRINT @SelectColumnist ----preparing the final SQL to be executed SELECT @SQL = N' SELECT --this is a fixed column list lt.stationID ,lt.entryDate ' --dynamic column list + @SelectColumnist +N' FROM TableA lt, ( SELECT * FROM ( SELECT colname,unit FROM TableB )p PIVOT ( MAX(p.unit) FOR p.colname IN ( '+ @pvtInColumnList +N' ) )q )rt ' PRINT @SQL EXECUTE sp_executesql @SQL </code></pre> <p><strong><em>here is the result</em></strong></p> <p><img src="https://i.stack.imgur.com/VoVe9.jpg" alt="enter image description here"></p> <p>ANSWER to your Second Question. the design above is not even giving performance nor flexibility. if user wants to add new Metadata (Column and Unit) that can not be done w/o changing table definition of TableA. if we are OK with writing Dynamic SQL to give user Flexibility we can redesign the TableA as below. there is nothing to change in TableB. I would convert it in to Key-value pair table. notice that StationID is not any more IDENTITY. instead for given StationID there will be N number of row where N is the number of column supplying the Values for that StationID. with this design, tomorrow if user adds new Column and Unit in TableB it will add just new Row in TableA. no table definition change required.</p> <pre><code> SET NOCOUNT ON IF OBJECT_ID('TableA_New','u') IS NOT NULL DROP TABLE TableA_New GO CREATE TABLE TableA_New ( rowID INT NOT NULL IDENTITY (1,1) ,stationID int not null ,entryDate datetime not null ,ColumnID INT ,Columnvalue NVARCHAR(MAX) ) GO IF OBJECT_ID('TableB_New','u') IS NOT NULL DROP TABLE TableB_New GO CREATE TABLE TableB_New ( id int not null IDENTITY(1,1) ,colname varchar(50) NOT NULL ,unit varchar(50) NOT NULL ) GO INSERT INTO TableB_New(colname,unit) SELECT 'WaterTemp','celcius' UNION ALL SELECT 'waterLevel','meters' UNION ALL SELECT 'Column3','unit3' UNION ALL SELECT 'Column4','unit4' UNION ALL SELECT 'Column5','unit5' UNION ALL SELECT 'Column6','unit6' INSERT INTO TableA_New (stationID,entrydate,ColumnID,Columnvalue) SELECT 1,'2013-01-01',1,2.4 UNION ALL SELECT 1,'2013-01-01',2,3.5 UNION ALL SELECT 1,'2013-01-01',4,101 UNION ALL SELECT 2,'2012-01-01',1,3.6 UNION ALL SELECT 2,'2012-01-01',2,9.9 UNION ALL SELECT 2,'2012-01-01',4,104 SELECT * FROM TableA_New SELECT * FROM TableB_New SELECT * FROM ( SELECT lt.stationID,lt.entryDate,rt.Colname,lt.Columnvalue + SPACE(3) + rt.Unit AS ColValue FROM TableA_New lt JOIN TableB_new rt ON lt.ColumnID=rt.ID )t1 PIVOT (MAX(ColValue) FOR Colname IN ([WaterTemp],[waterLevel],[Column1],[Column2],[Column4],[Column5],[Column6]))pvt </code></pre> <p>see the result below.</p> <p><img src="https://i.stack.imgur.com/ICgsa.jpg" alt="enter image description here"></p>
    singulars
    1. This table or related slice is empty.
    1. POCreate a view based on column metadata
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USAnup Shah
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCreate a view based on column metadata
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    1. COVote up for the effort, I will check your suggestions and get back to you asap. As for your second argument, I'm not sure why changing the table-definition would be such a bad idea: if the data structure changes, so should the table. But I'll take a look at your proposed design and get back to you.
      singulars
      1. PO
      1. USpkExec
    2. COno it is not bad, I was saying if you are ok with the way I have mentioned retrieving data using dynamic SQL then, by making a change in TableA definition you can get flexibility as well for user. doesn't that add a plus point for your application from user experience perspective? lets say new stationID is sending completely new measure Columns and with the new design you do not need to change TableA, or any CRUD API to access that.
      singulars
      1. PO
      1. USAnup Shah
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload