CODEWITHSUNDEEP
sql-servertableau-api
user3165474
user3165474

Reputation: 164

Tableau : How do i display the data?

I have a table called Fields in SQL Server:

              Fields
-------------------------------------
|ID (PK)| Field Number | Field Data |
-------------------------------------
|       |       3      |   123456   |
|   1   |       6      |   45678    |
|       |      13      |   58963    |
-------------------------------------
|       |      3       |   12347    |
|   2   |      8       |   456789   |
|       |      36      |   1234     |
-------------------------------------

What I'm trying to achieve using Tableau is :

         _____________________________________
        |             Field Number            |
        |_____________________________________|
        | 3     | 6    | 8    | 13   |   36   |
 _______|_______|______|______|______|________|
|       |       |      |      |      |        | 
|ID(PK) |       |      |      |      |        |
|_______|_______|______|______|______|________|
|       |       |      |      |      |        |
|   1   | 123456| 45678| NULL | 58963|  NULL  |
|_______|_______|______|______|______|________|
|       |       |      |      |      |        |
|   2   | 12347 | NULL |456789| NULL |  1234  |
|_______|_______|______|______|______|________|

Thanks in advance

Upvotes: 1

Views: 63

Answers (4)

JJBee
JJBee

Reputation: 56

  1. field number to columns row (rightclick n change to discrete)
  2. id(pk) to rows row (rightclick n change to discrete)
  3. field data to label mark

Upvotes: 0

Alex Blakemore
Alex Blakemore

Reputation: 11896

No need to write a SQL query for this when using Tableau, just connect to your table:

First make sure ID and Field Number are both discrete dimensions*, then

  1. Put ID on the rows shelf to get a row for each id.
  2. Put Field Number on the columns shelf to get a column for each Field Number
  3. Put Field Data on the Text shelf to complete your viz

*Right click on the fields in the data pane in the left margin to make those changes if needed.

Upvotes: 2

Ajay Dwivedi
Ajay Dwivedi

Reputation: 328

@Venkatesh's solution seems correct. Just to add, if you have multiple values for [FIELD_NUMBER] column, use can use below query to generate comma separated values.

DECLARE @myteam VARCHAR(MAX)
SELECT @myteam = COALESCE(@myteam+', ['+CAST(ID AS VARCHAR(10))+']','['+CAST(ID AS VARCHAR(10))+']') 
FROM    ( SELECT DISTINCT ID FROM (VALUES(1),(2),(3),(71),(32))AS MyTable(ID) ) T;--Replace your table here
PRINT @myteam

enter image description here

Upvotes: 0

Chanukya
Chanukya

Reputation: 5893

SELECT ID,[3], [6], [8],[13],[36]
FROM 
(
  SELECT ID,FIELD_NUMBER,FIELD_DATA
  FROM #A
) SRC
PIVOT
(
  MAX(FIELD_DATA)
  FOR FIELD_NUMBER IN ([3], [6], [8],[13],[36])
) PIV;

Upvotes: 2

Related Questions